Transactions and Locks

Question

I am currently working with transactions and getting confused. These transaction are created at the data access layer and not in the stored procedures of the database (SQL Server 2008). I understand the normal working of Isolation Levels set for a transaction. I am unable to comprehend what should happen in the following scenario.

1. Initiate a transaction
2. Select Employee with ID=1.
3. Update Employee with ID=1.
4. Commit

There are multiple threads doing the same thing but different ID. But there might be a case where in two threads look up the same ID. Lets call them Thread A and B. The above steps progress in the following manner with respect to the two threads. The Isolation Level is set to Repeatable Read.

A1. Initiate a transaction A2. Select Employee with ID=1. B1. Initiate a transaction B2. Select Employee with ID=1. A3. Update Employee with ID=1. A4. Commit B3. Update Employee with ID=1. B4. Commit

What I really want to achieve from the transaction is that when Thread A selects a particular record, Thread B should not even be able to select that record. I don't know if I am thinking on the right track by using transactions and locks for this scenario.

Awaiting replies :)

Answer 1

You should use an UPDLOCK table hint to prevent deadlocks, e.g.,

select * from employee with (updlock) where id = @id
update employee set name = @name where id = @id

Without this you could get deadlocks, because the select by default takes a shared read lock:

1. Transaction A does the select (shared read lock).
2. Transaction B does the select (shared read lock, could be on some of the same records as transaction A, e.g., if a page lock is taken).
3. Transaction A now does the update, which requires an exclusive write lock (lock escalation), but has to wait for transaction B to release its shared read lock.
4. Transaction B now also wants to do its update, but has to wait for transaction A to release its shared read lock.

So transaction A and B are now waiting for each other - classic lock escalation deadlock. The UPDLOCK table hint avoids this because it forces the select to take an exclusive lock:

1. Transaction A does the select (exclusive update lock).
2. Transaction B wants to do its select but has to wait for transaction A to release its lock first.
3. Transaction A now does the update, and commits, releasing the update lock taken by the select.
4. Transaction B can now do its select.

Edit: You can combine the UPDLOCK with a ROWLOCK to ask for a row level lock, e.g., "with (updlock, rowlock)". You can ask, but you may not always get it - see http://msdn.microsoft.com/en-us/library/ms187373(v=sql.100).aspx. Also row locks can be more expensive than page locks, because SQL Server will probably have a lot more locks to track if you use row locks. So I would let SQL Server choose for itself the extent of the lock, it normally does an OK job; in this case it shouldn't take a table lock. Only explicitly use a rowlock if you have a problem without it.

Also note that a rowlock on its own won't prevent a deadlock where two transactions select the same record (row) and then try to update it - so you always need an updlock for this.