Acquiring lock - C# SQL server

Question

Database : SQL server 2005

Programming language : C#

I have a method that does some processing with the User object passed to it. I want to control the way this method works when it is called by multiple threads with the same user object. I have implemented a simple locking which make use of database. I can't use the C#'s lock statement as this method is on a API which will be delivered on different machines. But the database is centralized.

Following code shows what I have. (Exception handled omitted for clarity)

Eg:

void Process(User user)
{
    using(var transaction = BeginTransaction())
    {
        if(LockUser()) {
            try {               
                /* Other processing code */
            }
            finally {
                UnLockUser();
            }
        }
    }
}

LockUser() inserts a new entry into a database table. This table has got a unique constraint on the user id. So when the second thread tries to insert the same data, constraint gets violated and will be an exception. LockUser() catches it and return false. UnlockUser just deletes the entry from the lock table.

Note: Please don't consider the possibilities of lock not getting deleted correctly. We have a SQL job that cleans items that are locked for long time.

Question

Consider two threads executing this method at same time and both of them started the transaction. Since transaction is committed only after all processing logic, will the transaction started on thread2 see the data inserted by thread1 to the lock table?

Is this locking logic OK? Do you see any problems with this approach?

Answer 1

If the acquisition of the lock - by virtue of inserting an entry into the database table - is part of the same transaction then either all or none of the changes of that transaction will become visible to the second thread. This is true for the default isolation level (ReadCommitted).

In other words: Whichever thread has a successful commit of that single transaction has also successfully acquired the lock (= inserted successfully the entry into the database).

In your code example I'm missing the handling of Commit()/Rollback(). Make sure you consider this as part of your implementation.

Answer 2

It depends on the transaction isolation level that you use. The default isolation (ReadCommitted) level assures that other connections cannot see the uncommitted changes that a connection is making.

When executing your SQL statement, you can explicitly acquire a lock by using locking hints.