Correct way to take a exclusive lock

Question

I am writing a procedure that will be reconciling finical transactions on a live database. The work I am doing can not be done as a set operation so I am using two nested cursors.

I need to take a exclusive lock on the transaction table while I am reconciling per client, but I would like to release the lock and let other people run their queries in between each client I process.

I would love to do a exclusive lock on a row level instead of a table level, but what I have read so far says I can not do with (XLOCK, ROWLOCK, HOLDLOCK) if the other transactions are running at READCOMMITED isolation level (which it is for me).

Am I taking a table level exclusive lock correctly, and is there any way in Server 2008 R2 to make row level exclusive locks work the way I want to without modifying the other queries running on the database?

declare client_cursor cursor local forward_only for 
     select distinct CLIENT_GUID from trnHistory
open client_cursor

declare @ClientGuid uniqueidentifier
declare @TransGuid uniqueidentifier

fetch next from client_cursor into @ClientGuid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
        begin tran

        declare @temp int

        --The following row will not work if the other connections are running READCOMMITED isolation level
        --select @temp = 1 
    --from trnHistory with (XLOCK, ROWLOCK, HOLDLOCK) 
    --left join trnCB with (XLOCK, ROWLOCK, HOLDLOCK) on trnHistory.TRANS_GUID = trnCB.TRANS_GUID
    --left join trnClients with (XLOCK, ROWLOCK, HOLDLOCK) on trnHistory.TRANS_GUID = trnClients.TRANS_GUID
    --(Snip) --Other tables that will be "touched" during the reconcile
    --where trnHistory.CLIENT_GUID = @ClientGuid

        --Works allways but locks whole table.
    select top 1 @temp = 1 from trnHistory with (XLOCK, TABLOCK) 
    select top 1 @temp = 1 from trnCB with (XLOCK, TABLOCK)
    select top 1 @temp = 1 from trnClients with (XLOCK, TABLOCK)
    --(Snip) --Other tables that will be "touched" during the reconcile

        declare trans_cursor cursor local forward_only for 
                select TRANS_GUID from trnHistory where CLIENT_GUID = @ClientGuid order by TRANS_NUMBER
        open trans_cursor

        fetch next from trans_cursor into @TransGuid
        WHILE (@@FETCH_STATUS <> -1)
        BEGIN
            IF (@@FETCH_STATUS <> -2)
            BEGIN

                --Do Work here

            END
            fetch next from trans_cursor into @TransGuid
        END

        close trans_cursor
        deallocate trans_cursor

            --commit the transaction and release the lock, this allows other 
            -- connections to get a few queries in while it is safe to read.
        commit tran
    END

    fetch next from client_cursor into @ClientGuid
END 

close client_cursor
deallocate client_cursor

Answer 1

I couldn't believe that an XLOCK would not block a concurrent reader at read committed so I just reproduced it: It is true. Script:

Session 1:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

SELECT * FROM T WITH (ROWLOCK, XLOCK, HOLDLOCK /*PAGLOCK, TABLOCKX*/) WHERE ID = 123

Session 2:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

SELECT * FROM T WHERE ID = 123

Plug in some table name that you have at hand. Session 2 is not being blocked.

I also tried using a PAGLOCK but that didn't work either. Next I tried a TABLOCKX but that didn't work either!

So your table-lock based strategy does not work. I think you'll have to modify the readers so that they either

1. use snapshot isolation to get a consistent view (as of before any writes)
2. use a higher isolation level to be blocked by the writer

Of course there is a nasty workaround to really, really lock the table: alter its schema. This will take a Sch-M lock which conflicts with basically any access to the table. It even holds of some metadata read operations. It could look like this:

--just change *any* setting in an idempotent way
ALTER TABLE T SET (LOCK_ESCALATION = AUTO)

I tested this to work.

---

Is SQL Server right not obeying the XLOCK? Or is this a flaw in the product? I think it is right because it conforms to the documented properties of READ COMMITTED. Also, even using SERIALIZABLE there are cases where one transaction can lock a row exclusively and another can read the same row! This can happen in the presence of indexes. One transaction might X-lock on the non-clustered index IX_T_SomeCol while another happily reads off of the clustered index PK_T.

So it is actually quite normal that transactions can execute independently even in the presence of exclusive locking.

Answer 2

If you are only worried about other readers, then you shouldn't need exclusive locks, the pattern

Begin Transaction

  Make Data Inconsistent

  Make Data Consistent

Commit Transaction

Should be fine. The only sessions who will see inconsistent data are those that use nolock or Read Uncommitted, or those that expect to make multiple consistent reads without using Repeatable Rows or Serializable.

In answer to the question, the correct way to take an exclusive lock, in my opinion, is to arrange things so the engine does it for you.