Databases
StackOverflow2013
Postsdbo
POsql locking for batch updates
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POsql locking for batch updates
    primarykey
    data
    text
    <p>I'm using SQL Server 2008 R2 and C#.</p> <p>I'm inserting a batch of rows in SQL Server with a column <strong>Status</strong> set with value <strong>P</strong>. Afterwards, I check how many rows already have the status <strong>R</strong> and if there are less than 20, I update the row to Status <strong>R</strong>. While inserting and updating, more rows are getting added and updated all the time.</p> <p>I've tried transactions and locking in multiple ways but still: at the moment that a new batch is activated, there are more than 20 rows with status <strong>R</strong> for a few milliseconds. After those few milliseconds it stabilizes back to 20.</p> <p>Does anyone have an idea why at bursts the locking doesn't seem to work? Sample code, reasons, whatever you can share on this subject can be useful! Thanks!</p> <p>Following is my stored proc:</p> <pre><code> DECLARE @return BIT SET @return = -1 DECLARE @previousValue INT --insert the started orchestration INSERT INTO torchestrationcontroller WITH (ROWLOCK) ([flowname],[orchestrationid],[status]) VALUES (@FlowName, @OrchestrationID, 'P') --check settings DECLARE @maxRunning INT SELECT @maxRunning = maxinstances FROM torchestrationflows WITH (NOLOCK) WHERE [flowname] = @FlowName --if running is 0, than you can pass, no limitation here IF( @maxRunning = 0 ) BEGIN SET @return = 1 UPDATE torchestrationcontroller WITH(ROWLOCK) SET [status] = 'R' WHERE [orchestrationid] = @OrchestrationID END ELSE -- BEGIN RETRY: -- Label RETRY BEGIN TRY SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION T1 --else: check how many orchestrations are now running --start lock table DECLARE @currentRunning INT SELECT @currentRunning = Count(*) FROM torchestrationcontroller WITH (TABLOCKX) --Use an exclusive lock that will be held until the end of the transaction on all data processed by the statement WHERE [flowname] = @FlowName AND [status] = 'R' --CASE IF( @currentRunning &lt; @maxRunning ) BEGIN -- less orchestrations are running than allowed SET @return = 1 UPDATE torchestrationcontroller WITH(TABLOCKX) SET [status] = 'R' WHERE [orchestrationid] = @OrchestrationID END ELSE -- more or equal orchestrations are running than allowed SET @return = 0 --end lock table SELECT @Return COMMIT TRANSACTION T1 END TRY BEGIN CATCH --PRINT 'Rollback Transaction' ROLLBACK TRANSACTION IF ERROR_NUMBER() = 1205 -- Deadlock Error Number BEGIN WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms GOTO RETRY -- Go to Label RETRY END END CATCH </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShhoud
    1. UShhoud
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COObviously if two threads read the count at the same time, and then go on to update, the number of rows can exceed 20. Some example code would help diagnose the exact situation.
      singulars
      1. POsql locking for batch updates
      1. USAndomar
    2. COI've added my code, hopefully you can help more with that. I thought the problem was like you said but is there a way to solve that? I thought the ISOLATION LEVEL might help with that.
      singulars
      1. POsql locking for batch updates
      1. UShhoud
    3. COThe free pass for maxRunning = 0 seems fishy... 50 threads might read that, and then update 50 rows
      singulars
      1. POsql locking for batch updates
      1. USAndomar
 

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