Wednesday, April 19, 2006

best practice for avoiding dirty read

for transactions or triggers that do any data manipulation routine(UPDATE,DELETE,INSERT?), follow guideline below:

for MS SQL, always put TABLOCKX in SELECT clause:
SET @PreviousQty = (SELECT Qty FROM Inventory WITH(TABLOCKX) WHERE ItemCode = @ItemCode

FOR PostgreSQL, always put FOR UPDATE in SELECT clause:
m_PreviousQty = (SELECT Qty FROM Inventory WHERE ItemCode = m_ItemCode FOR UPDATE)

always put row lock even if the SELECTED will not be the table to be written upon. this will ensure preventing dirty reads from other concurrently executing transactions that modifies table you read.

Note regarding row-level locking:
WITH(TABLOCKX) and FOR UPDATE can also be used to implement row-level pessimistic locking for LAN-based application transactions, i.e. when you open one record, no other program can read the same record, this is called pessimistic locking.


Post a Comment

Links to this post:

Create a Link

<< Home