mysql - Minimal example of using select... for update to isolate rows

Question

UPDATE: There is now SKIP LOCKED and NOWAIT for Mysql and Postgres.

Old question follows.

---

I want concurrent transactions to select a row from the table, marking it as "dirty" so that other transactions cannot select it, then performing the rest of the transaction.

I had trouble using select... for update for this purpose as the second transaction contends for the same. Please provide a minimal example for different transactions to select distinct rows.

My data is:

mysql> select * from SolrCoresPreallocated;
+----+-------------+-----+-----+
| id | used_status | sid | cid |
+----+-------------+-----+-----+
|  1 |           0 |   0 | 400 |
|  2 |           0 |   0 | 401 |
|  3 |           0 |   0 | 402 |
|  4 |           0 |   0 | 403 |
|  5 |           0 |   0 | 404 |
|  6 |           0 |   0 | 405 |
+----+-------------+-----+-----+
6 rows in set (0.00 sec)

And this stuff is not working as expected:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from SolrCoresPreallocated order by id limit 1 for update;
+----+-------------+-----+-----+
| id | used_status | sid | cid |
+----+-------------+-----+-----+
|  1 |           0 |   0 | 400 |
+----+-------------+-----+-----+
1 row in set (0.00 sec)

...set the used_status to 1
...perform the rest of the operations

...as the second transaction onward

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from SolrCoresPreallocated order by id limit 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>  rollback;
Query OK, 0 rows affected (0.00 sec)

Answer 1

SELECT ... FOR UPDATE locks the row(s) in exclusive mode, which means the second select cannot proceed until the first one has completed or rolled back. This is since the second select's result could be affected by the content of the row you've locked, so it needs to get a read lock to the row to check.

If you create a UNIQUE INDEX on for example id, you could do;

select * from SolrCoresPreallocated where id=1 for update;

in the first transaction and;

select * from SolrCoresPreallocated where id=2 for update;

in the second one independently, since the unique index lets the second select find the correct row without read-locking the first one.

EDIT: To get a "free" row as quickly as possible, the only way really is to do two transactions;

• BEGIN/SELECT FOR UPDATE/UPDATE to busy/COMMIT to get the row.
• BEGIN/<process row>/UPDATE to free/COMMIT to process the row and release it.

This means that you may need compensating actions in case a process fails and rolls back the transaction that would UPDATE the row to free, but since MySQL (or standard SQL for that matter) doesn't have a notion of "get the next unlocked row", you don't have many options.