Take the 2-minute tour ×

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 :)

share|improve this question
In general, is it OK for you that Thread B updates record immediately after Thread A? I.e. changes made by Thread A will be lost. If this is not OK for you, then you should look into optimistic concurrency control. –  Alexander Sep 27 '12 at 8:11
It is not OK for Thread B to update the record. I even want to prevent the record from being selected when it has already been selected by A in the transaction. –  Sharkz Sep 28 '12 at 0:05
add comment

5 Answers

up vote 1 down vote accepted

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.

share|improve this answer
Will the updlock lock the particular row that has been selected or will it lock the whole table or a page lock. ID is the primary key so it selects only one record always. If it locks only that particular row then this is the solution I have been looking for. –  Sharkz Sep 28 '12 at 0:16
Please see my edit. –  ShellShock Sep 28 '12 at 10:11
add comment

You should have a look at optimistic locking, it works by adding a extra check on the update where you check if the record is not changed between the read and the write. You can also read your record outside of your transaction scope, which gives you better performance overall.

Optimistic_concurrency_control wikipedia

share|improve this answer
I'm not sure if that would help in this particular situation. Optimistic locking would enable Thread B to execute Select Employee with ID=1 without waiting for transaction from Thread A. In the end Thread B would update the employee also. –  OttO Sep 27 '12 at 7:58
Optimistic concurrency control allows multiple transactions to access one record and make changes to it if it has not been modified. But in my case I don't wan't to allow multiple transaction to even select the same record. –  Sharkz Sep 28 '12 at 0:10
add comment

Transaction Only helps you to protect data. you have look at the lock mechanism to handle your problem.

share|improve this answer
Can you elaborate on how lock mechanism might be able to help me in this scenario? –  Sharkz Sep 28 '12 at 0:11
add comment

I seems to me you should be looking at threading mechanism you're using. You should be able to know upfront (not during transaction) and not start a thread with the ID that's already being processed. Or youre threads should have access to some shared synchronized list with ID's that should be processed. That way two threads cant work on the same ID.

share|improve this answer
add comment

Try something like this:

using System;
using System.Transactions;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace StackOverflow.Demos
    class Program

        static Database db = DatabaseFactory.CreateDatabase("demo");

        public static void Main(string[] args)
            TransactionOptions options = new TransactionOptions();
            options.IsolationLevel = System.Transactions.IsolationLevel.RepeatableRead; //see http://www.gavindraper.co.uk/2012/02/18/sql-server-isolation-levels-by-example/ for a helpful guide to choose as per your requirements
            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
                using (IDbConnection connection = db.CreateConnection())
                    connection.Open(); //nb: connection must be openned within transactionscope in order to take part in the transaction
                    IDbCommand command = connection.CreateCommand();

                    command.CommandType = CommandType.Text;
                    command.CommandText = "select top 1 status from someTable with(UPDLOCK, ROWLOCK) where id = 1"; //see http://msdn.microsoft.com/en-us/library/aa213026(v=sql.80).aspx
                    string statusResult = command.ExecuteScalar().ToString();

                    if (!statusResult.Equals("closed",StringComparison.OrdinalIgnoreCase))
                        command.CommandType = CommandType.Text;
                        command.CommandText = "update someTable set status='closed' where id = 1";


ps. generally it's recommended that you use stored procedures over hardcoded SQL as I've done above - if you can push all of your logic into the stored procs such that you just make one call to the proc and all logic's handled within the database go that way.

In the above example you'll notice the namespace:


That's there because I tend to use MS's Data Block from their Enterprise Library, which gives you a load of functionality on top of the OOTB libraries. If you're interested you can read more on that here: http://msdn.microsoft.com/library/cc467894.aspx

share|improve this answer
I myself use the data block for all DB related functionality. The scenario that you have given, inside the if condition I cannot directly set the status, I call another web service which provides a response and based on that response I have to update the status. And since its a web request which might take time and within this period another request may come in to my server and select the same record and try to use it. I want to stop this other request to even perform the select on the same record. –  Sharkz Oct 22 '12 at 4:20
add comment

Your Answer


