0

We have very some legacy old transactional tables which contains columns Locked and LockDate. Only one user can pick a transaction(row) at a time. Whenever user pick a speecfic transaction, we are marking the row Locked=1 so no other user can pick the same transaction(until user save the transaction or we clear the lock after some time). The problem is that under system load, multiple user can pick the same transaction under concurrency. We are doing,

SELECT TOP 1 * FROM TABLE WHERE Locked=0 AND OurConditions
UPDATE TABLE SET Locked=1 WHERE ID=....

How to fix this issue with minimal performance impect on system.

Imran Qadir Baksh - Baloch
  • 32,612
  • 68
  • 179
  • 322
  • You don''t implement checkin/checkout functionality by holding locks. That's going to decimate scalability without solving the concurrency problems. Transactions and *locks* are meant to be short-lived. – Panagiotis Kanavos Nov 25 '19 at 13:01
  • 1
    You can use an `UPDATE WHERE` is you want to ensure only one connection at a time can checkout/lock. Why is `TOP 1` used though? Are you trying to implement a *queue*? That's a very different *and* very difficult problem – Panagiotis Kanavos Nov 25 '19 at 13:03
  • @PanagiotisKanavos I wanna simple one user get one transaction at a time for processing purpose. – Imran Qadir Baksh - Baloch Nov 25 '19 at 13:16

1 Answers1

2

Add locked to your WHERE:

UPDATE YT
SET Locked = 1--, other columns to set
FROM dbo.YourTable YT
WHERE YT.ID = @ID
  AND Locked = 0;

Then someone else can't lock the "row" when it's already locked.

Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Means even under multiple concurrent requests only one will get the lock – Imran Qadir Baksh - Baloch Nov 25 '19 at 13:03
  • 1
    @user960567 the `UPDATE WHERE` ensures that. Why did you use that `TOP 1` though? Are you trying to implement a queue? That's a far harder problem – Panagiotis Kanavos Nov 25 '19 at 13:03
  • Yes, @user960567 because as the `UPDATE` is processed the row/table will be locked. A second `UPDATE` won't be able to be processed until that first lock is released, and as the underlying data has then changed, the `UPDATE` won't return any rows in the `WHERE`; thus won't be updated. – Thom A Nov 25 '19 at 13:04
  • @Larnu using `Locked` and `locks` in this context isn't a good choice as *database* locks aren't used. The row will be flagged/checked out even if the connection closes – Panagiotis Kanavos Nov 25 '19 at 13:06
  • Under concurrency how do I know which one is updated and which one not? – Imran Qadir Baksh - Baloch Nov 25 '19 at 13:08
  • Not sure I follow you there, @PanagiotisKanavos . it's the table locked that helps the desired behaviour. – Thom A Nov 25 '19 at 13:08
  • @user960567 I assume you're setting the name of the person who locked it in the table, so you'd know by looking at that column. – Thom A Nov 25 '19 at 13:10
  • If no record updated then I need to select another ID? I am giving user one transaction to one user – Imran Qadir Baksh - Baloch Nov 25 '19 at 13:12
  • Then what you're after is a queueing system, as @PanagiotisKanavos asked you about in both this answer and question, which you haven't responded to. – Thom A Nov 25 '19 at 13:14
  • @PanagiotisKanavos what do you suggest for selecting one transaction per user at time? – Imran Qadir Baksh - Baloch Nov 25 '19 at 13:14
  • 2
    @user960567 don't use a database table in the first place. If you have to, use a *separate* queue table and use destructive dequeuing using DELETE OUTPUT, [as shown here](https://rusanu.com/2010/03/26/using-tables-as-queues/). If you try to implement queues with UPDATE and locking [as shown here](https://stackoverflow.com/questions/939831/sql-server-process-queue-race-condition/940001) you'll find that normal access to the table will cause problems with the queueing scenario in rare conditions, which occur at midnight or when you're on vacation – Panagiotis Kanavos Nov 25 '19 at 13:19