Questions about concurrency and locks

jackymeng

+ ---- + ---------- + --------- +
| id | item | status |
+ ---- + ---------- + --------- +
| 1 | a | 1 |
+ ---- + ---------- + --------- +
| 2 | b | 0 |
+ ---- + ---------- + --------- +
| 3 | c | 0 |
+ ---- + ---------- + --------- +
| 4 | d | 0 |
+ ---- + ---------- + --------- +
| 5 | e | 0 |
+ ---- + ---------- + --------- +
| 6 | f | 0 |
+ ---- + ---------- + --------- +
| 7 | g | 0 |
+ ---- + ---------- + --------- +

There is a program that polls this table every time and selects the record with select top 1 * from tbl where status = 0, and then sets the status of this record to 1. The problem is that if it is multi-threaded, a thread may appear. This record has been fetched. Before it sets this record to 1, another thread fetches this record, and it appears that multiple identical data records have been fetched. How do you solve this problem?

我是杀人狂

When fetching, add non-zero conditions?

jackymeng

First take 0 and then update the retrieved record to 1

sirenyun

You can update the status to 1:
update tbl set status = 1 from (select top 1 id from tbl where status=0) as t1 where tbl.id = t1.id
, And then use the trigger to get the information of the most recently updated row. This can avoid the problems mentioned by the original poster!

sirenyun

The statement in the trigger is written like this
if update(status)
begin
     select * from deleted, inserted where deleted.status = 0 and inserted.status = 1
end

sirenyun

There are too many return columns written above, it should be like this:
if update(status)
begin
     select deleted.id,deleted.item,deleted.status from deleted,inserted where deleted.status = 0 and inserted.status = 1
end