Friday, January 30, 2009

REPEATABLE READ is fine, but want to let others to read non-locked records

We usually set the REPEATABLE READ Transaction Isolation Level in order to overcome Inconsistent analysis (nonrepeatable read) and Lost Update concurrency problems. This level do not let other users to read records that are read by your transaction, but creates a deadlock situation. Here is a simple example;
   1: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
   2: BEGIN TRAN
   3:     DECLARE @Id int
   4:     SELECT TOP 1 @Id = Id
   5:     FROM dbo.Employee
   6:     WHERE IsUpdated = 0
   7:  
   8:     ......... -- more processing
   9:  
  10:     UPDATE dbo.Employee
  11:         SET Info = 'some text', IsUpdated = 1
  12:     WHERE Id = @Id
  13: COMMIT TRAN 
This code gets the first record available for updating (TOP 1, IsUpdated = 0) and does some processing, finally updates the record. This code does not allow others to modify the record while it is being accessed, avoiding inconsistent analysis and lost updates. But it reduces the concurrency. It does not allow other to get the second available record too, making a deadlock situation if the same is executed by another connection. There is no harm of letting others to access the second available record. How do we do it? This cannot be implemented with REPEATABLE READ isolation level. All we have to do is, use UPDLOCK and READPAST query hints. UPLOCK puts exclusive lock on the object and does not allow others to obtain a lock on the same. READPAST skips all locked records and read the non-locked records. Here is the corrected code;
   1: -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
   2: BEGIN TRAN
   3:     DECLARE @Id int
   4:     SELECT TOP 1 @Id = Id
   5:     FROM dbo.Employee
   6:     WITH (UPDLOCK, READPAST)
   7:     WHERE IsUpdated = 0
   8:  
   9:     ......... -- more processing
  10:  
  11:     UPDATE dbo.Employee
  12:         SET Info = 'some text', IsUpdated = 1
  13:     WHERE Id = @Id
  14: COMMIT TRAN 
This locks the record while it is being accessed but allows to process the next available record for others.

No comments:

Post a Comment