CODEWITHSUNDEEP
c#sql-server-2008transactionslocks
Sharkz
Sharkz

Reputation: 458

Transactions and Locks

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

Upvotes: 3

Views: 11267

Answers (4)

JohnLBevan
JohnLBevan

Reputation: 24410

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";
                    }

                    scope.Complete();
                }
            }
        }
    }
}

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:

Microsoft.Practices.EnterpriseLibrary.Data;

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

Upvotes: 1

Polyfun
Polyfun

Reputation: 9639

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.

Upvotes: 5

OttO
OttO

Reputation: 419

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.

Upvotes: -1

Peter
Peter

Reputation: 27934

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

Upvotes: 1

Related Questions