1

The flow for locking I have currently is a SELECT ... FOR UPDATE followed by an INSERT. e.g.

BEGIN;
SELECT count(*) FROM test WHERE col = 1 FOR UPDATE;
# check to ensure an invariant will still be met after the insert.
INSERT INTO test (col) VALUES (1);
COMMIT;

I expected the SELECT above to acquire an exclusive lock on something to prevent multiple callers from being in the critical section at the same time but it appears that by default, InnoDB only acquires a shared lock on a table or index gap. If two concurrent transactions attempt this at the same time, they can both acquire the shared lock but one will fail with a deadlock error when trying to insert.

Is there any way to make this code behave more like standard pessimistic locking to prevent concurrent readers?

Note: I am aware that in some cases, a unique index can solve this issue but I have come across two instances where a unique index is insufficient (1) when one of the columns is nullable and I want exactly one null value and (2) when I want to guarantee another invariant about the rows like there should be less than 10 rows with a given key.

Barmar
  • 741,623
  • 53
  • 500
  • 612
dolan
  • 1,716
  • 11
  • 22
  • Consider `SELECT ... WITH SHARED LOCK` or `GET_LOCK()`. – Rick James Jul 03 '19 at 17:08
  • Why do you think SELECT FOR UPDATE acquires a shared lock? It does acquire an exclusive lock. I just tested it, and found that after one session does the SELECT FOR UPDATE, a second session that tries the same SELECT is blocked until the first session does COMMIT. – Bill Karwin Jul 03 '19 at 18:25
  • @BillKarwin I am thinking specifically about the case where a row doesn't yet exist so Innodb has to do a gap lock (https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html#innodb-gap-locks). From the link: "ap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist." – dolan Jul 03 '19 at 18:33
  • @RickJames Thanks for the suggestion. But if it's shared lock does that lead to the same deadlocking issue? – dolan Jul 03 '19 at 18:35
  • SELECT WITH SHARED LOCK would be a shared lock like a gap lock so that wouldn't help. You'll need to use `GET_LOCK()`. – Bill Karwin Jul 03 '19 at 19:01

0 Answers0