How to get the first available row in simultaneous connections?

Question

I assign the first available row to each coming user as

SELECT id FROM table1 WHERE status IS NULL ORDER BY id LIMIT 1
fetched id is xxx
UPDATE table1 SET status='taken' WHERE id=xxx

How can I make sure that not a second user retrieve the same id before it is UPDATEd as taken?

NOTE: It has nothing to do with INSERT. The table is already there. Users should take the first available row. UPDATE is just to keep track of taken rows.

If id is **auto_incremented** and is the **primary key**, you shouldn't have to care about this. — Dash, Apr 21 '17 at 13:12
@Maincore unless another user is created, before updating the first one. — Ivanka Todorova, Apr 21 '17 at 13:13
Why not use sub query as `UPDATE table1 SET status='taken' WHERE id =(SELECT id FROM table1 ORDER BY id LIMIT 1)` — Saty, Apr 21 '17 at 13:16

Ivanka Todorova · Accepted Answer · 2017-04-21T14:50:50.417

1

Use transactions:

start transaction;
SELECT @A:=id FROM table1 ORDER BY id LIMIT 1
UPDATE table1 SET status='taken' WHERE id=@A
commit;

edited Apr 21 '17 at 14:50

answered Apr 21 '17 at 13:12

Ivanka Todorova

9,964
16
66
103

score 0 · Answer 2 · answered Apr 21 '17 at 13:20

0

Transactionless version:

SELECT id FROM table1 WHERE status != 'taken' ORDER BY id LIMIT 1
fetched id is xxx
UPDATE table1 SET status='taken' WHERE id=xxx AND status != 'taken'
check number of affected rows

answered Apr 21 '17 at 13:20

Alex Blex

34,704
7
48
75

What if `affected_rows=0`? repeating the process again and again? – Googlebot Apr 21 '17 at 13:26
Up to you really. What you gonna do when transaction fails (referring to @ivanka-todorova 's answer)? – Alex Blex Apr 21 '17 at 13:31

How to get the first available row in simultaneous connections?

2 Answers2