Databases
StackOverflow2013
Postsdbo
POCorrect way to take a exclusive lock
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCorrect way to take a exclusive lock
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USScott Chamberlain
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POCorrect way to take a exclusive lock
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POCorrect way to take a exclusive lock
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POCorrect way to take a exclusive lock
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI'm trying to figure out why you need an exclusive lock. Are other people likely to be inserting records? Other people updating records? Are you worried about other people getting an inconsistent view of the data?
      singulars
      1. POCorrect way to take a exclusive lock
      1. USLaurence
    2. CO@Laurence I am worried about other people getting a inconsistent view state. I am attempting to correct a error that affected a small % of clients, however that correction process leaves several interdependent rows in several tables (I actually will be locking on 5 tables but simplified my code example to one table) in a inconsistent state during the correction process. The inconsistency is isolated per client, but doing a `SELECT SUM(ColA) FROM trnHistory` over that one client would return a incorrect value during the "correction" process. So I need to take a exclusive lock to prevent reads.
      singulars
      1. POCorrect way to take a exclusive lock
      1. USScott Chamberlain
    3. COI don't understand why a transaction wouldn't protect you from this, unless you have people doing read_uncommitted.
      singulars
      1. POCorrect way to take a exclusive lock
      1. USLaurence
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload