title: Correct way to take a exclusive lock
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>I would love to do a exclusive lock on a row level instead of a table level, but <a href="https://stackoverflow.com/a/4609384/80274">what I have read so far</a> says I can not do <code>with (XLOCK, ROWLOCK, HOLDLOCK)</code> if the other transactions are running at <code>READCOMMITED</code> isolation level (which it is for me).</p>
  
  <p>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?</p>
  
  <pre><code>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 &lt;&gt; -1)
  BEGIN
      IF (@@FETCH_STATUS &lt;&gt; -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 &lt;&gt; -1)
          BEGIN
              IF (@@FETCH_STATUS &lt;&gt; -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
  </code></pre>