Select just one row from a table and lock it

Question

I have a table with multiple rows. I also have a multithreaded application which reads a row with status = 1 and then changes it to status = 2 after reading.

However, due to the multithreaded nature of the application, it keeps reading the same row twice (by different threads). I know this is a concurrency problem but I am unable to solve it.

As of now, I am reading the row as:

SELECT TOP 1 * FROM Inbox WHERE Status = 1 ORDER BY ID DESC;

Then, using the ID, I update the row:

UPDATE Inbox SET Status = 2 WHERE ID = X;

I would like a query to lock a row as it selects its ID and returns it so no other threads are able to read it.

Have you tried using a `rowversion` column? – DavidG Dec 01 '14 at 09:08 — DavidG, Dec 01 '14 at 09:08
What language is your application written in? – Jaques Dec 01 '14 at 09:14 — Jaques, Dec 01 '14 at 09:14
@DavidG How would I go around using that? – mimmi Dec 01 '14 at 09:16 — mimmi, Dec 01 '14 at 09:16

score 1 · Answer 1 · answered Dec 01 '14 at 09:17

One possible solution is to add a ROWVERSION column to your table. This creates a column that updates automatically whenever you run an UPDATE to a row. Using it in your queries means you can check if another process has already touched the same row. First add the column:

ALTER TABLE Inbox
    ADD RowVersion ROWVERSION

Now you change your UPDATE query to take it in to account:

UPDATE Inbox SET Status = 2 WHERE ID = X AND RowVersion = @RowVersion

Check the number of rows updated and you know know if you have been the first person to try.

SELECT @@ROWCOUNT

Alternatively, using the MSDN docs for ROWVERSION you can do something like this:

DECLARE @t TABLE (myKey int);

UPDATE Inbox
SET Status = 2
    OUTPUT inserted.myKey INTO @t(X) 
WHERE ID = X 
AND [RowVersion] = @RowVersion

IF (SELECT COUNT(*) FROM @t) = 0
BEGIN
    RAISERROR ('Error changing row with ID = %d'
        ,16 -- Severity.
        ,1 -- State 
        ,X) -- Key that was changed 
END

score 1 · Answer 2 · answered Dec 01 '14 at 09:20

The best solution for multi threaded applications to read from the same table and make sure no other thread gets the same record is to create a static class that will handle the selection from your table where Status = 1 and let the threads get a record from the class. This will solve your problem. In your threads you can handle the records accordingly.

So create a static class that Refresh the data from your table, add a Method GetNextRecord that will return a DataRow with the appropriate locking in place. Before starting the threads execute Refresh method, and start your threads. Each thread will execute GetNextRecord until the result is null then terminate. When all your threads is done, start again.

This worked for me in a similar solution.

Hope it helps.

This would have worked, however I will also be running multiple instances of the same application and the problem of concurrency comes up again — mimmi, Dec 02 '14 at 03:15

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

Here is an illustration how can you achieve this :

create Table

create table Inbox  ( id int  primary key clustered, stts int )

insert into Inbox values
(1,1),  (2,1),  (3,1),  (4,1)

now, open two tabs in your SMSS and write this in both :

begin tran
select top 1 * from Inbox with(readpast,updlock) where stts = 1 order by id desc
--rollback tran

now, run the first and check what it returned. Then, go to second and run it and check the result. both are giving different results. so, assume both the tabs as different threads and you will get the idea how to achieve it. now uncomment rollback tran and execute it. conclusion is you need to create a transaction boundary, select your data within the transaction boundary with lock hints 'readpast,updlock' do your stuff with that data and in the end commit the transaction.

Note : This is how I implemented multithreading job processor in C++ so it might not work for you. If the query second tab stucks and doesn't give you result then you need to create an index.

Check similar question here and a useful information here.

edited May 23 '17 at 11:57

Community

1
1

answered Dec 01 '14 at 09:33

Deep

3,162
1
12
21

I ran multiple threads with status = status + 1 and checked if any were more than 2 after the job was complete. Working perfectly, doesn't require changes to application or database. – mimmi Dec 02 '14 at 09:55
You might still run into a race condition when 2 queries run at exactly the same time, or will SQL handle it properly? – Jaques Dec 02 '14 at 12:06
@Jaques I am using this solution in a live credit card project and it had never given any problem except indexing (check the second link of my answer for that). `readpast, uplock` within a transaction boundary ensures that two/more transactions from different threads should not take lock on same row even if that query is fired by them simultaneously. I vaguely remember that this is called `concurrency control` in technical terms... – Deep Dec 04 '14 at 05:46

score 1 · Answer 4 · answered May 19 '18 at 12:44

Maybe you can use SELECT ... FOR UPDATE if database in (postgresql, oracle, mysql).

It will lock rows until the end of the transaction.

START TRANSACTION;
# reading the row as
SELECT TOP 1 * FROM Inbox WHERE Status = 1 ORDER BY ID DESC;
# Then, using the ID and update the row
UPDATE Inbox SET Status = 2 WHERE ID = X;
COMMIT;

Mysql innodb-locking-reads

Select just one row from a table and lock it

4 Answers4