Multiple SELECT ... FOR UPDATE with a delayed INSERT INTO

Question

I am having an issue with SELECT ... FOR UPDATE and INSERT INTO statements on separate connections deadlocking.

Given an empty table tblFoo with the primary key id, consider the following pseudocode:

function locate(array values) {

    BEGIN TRANSACTION;

    rows = SELECT * FROM tblFoo WHERE id IN values FOR UPDATE;

    if (rows is empty) {
        sleep(10); // i.e., do some stuff
        rows = INSERT INTO tblFoo (id) VALUES values;
    }

    COMMIT;

    return rows;
}

On process A @ t=0: return locate([1,2,3]);

On process B @ t=1: return locate([1]);

My expectation is that process 1 would gap lock rows with ids 1, 2, 3 which blocks process B at the SELECT ... FOR UPDATE until process A's transaction is committed. Once committed, process B gets unblocked and returns the row with id 1 which was just inserted by process A.

The observed behavior is that a deadlock is encountered, causing process A to roll back and process B inserts a row with id 1.

Can anyone help me understand why MySQL is behaving this way?

I am using innoDB with MySQL version 5.5.

Edit: The following is the table structure

CREATE TABLE `tblFoo` (
    `id` INT(11) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

Edit 2: The following is the innoDB status detailing the deadlock

------------------------
LATEST DETECTED DEADLOCK
------------------------
161205 15:55:50
*** (1) TRANSACTION:
TRANSACTION 32A3E743A, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s)
MySQL thread id 12243323, OS thread handle 0x7fd7dd47f700, query id 4713227035 localhost root update
INSERT INTO test.tblFoo (id) VALUES (1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1556528 n bits 72 index `PRIMARY` of table `test`.`tblFoo` trx id 32A3E743A lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00032a3e5f6b; asc   *>_k;;
 2: len 7; hex b30017d06b0110; asc     k  ;;

*** (2) TRANSACTION:
TRANSACTION 32A3E5FD3, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1248, 5 row lock(s)
MySQL thread id 12243319, OS thread handle 0x7fd7f0097700, query id 4713230393 localhost root update
INSERT INTO test.tblFoo (id) VALUES (1),(2),(3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 1556528 n bits 72 index `PRIMARY` of table `test`.`tblFoo` trx id 32A3E5FD3 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000005; asc     ;;
 1: len 6; hex 00032a38e424; asc   *8 $;;
 2: len 7; hex cc001c166a0110; asc     j  ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00032a3e5f6b; asc   *>_k;;
 2: len 7; hex b30017d06b0110; asc     k  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 1556528 n bits 72 index `PRIMARY` of table `test`.`tblFoo` trx id 32A3E5FD3 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00032a3e5f6b; asc   *>_k;;
 2: len 7; hex b30017d06b0110; asc     k  ;;

*** WE ROLL BACK TRANSACTION (2)

I suppose `id` is a `PRIMARY KEY`, but please show your table structure anyway. — bishop, Dec 05 '16 at 15:56
The `sleep(10)` I guess you put it to represent some other activities. Do they really take 10 seconds? If they do, I wouldn't recommend to open a transaction that lasts that much. — FDavidov, Dec 05 '16 at 16:00
@FDavidov Yeah, I know it's not great practice but the `sleep(10)` is an operation that can't be avoided and must be performed exactly once per `id`. — MrDiggles, Dec 05 '16 at 16:05
If this is your actual table structure, then perhaps you're hitting [MySQL "bug" #72439](https://bugs.mysql.com/bug.php?id=72439) and should try a different algorithm. If this isn't your actual table structure, perhaps you need to [drop indexes on other columns](http://stackoverflow.com/a/5432714/2908724). — bishop, Dec 05 '16 at 16:11
Can you post the detailed deadlock info from `show engine innodb status`? — Vatev, Dec 05 '16 at 16:13
Break out [innotop](https://github.com/innotop/innotop) and see if it hints at the contention (likely the insert intentions on id=1; suggest doing a `COMMIT` after each `INSERT` to fix). — bishop, Dec 05 '16 at 16:41
If you can pepper it with `COMMITs`, you can probably simply do `INSERT IGNORE` and get rid of the `SELECT`. — Rick James, Dec 05 '16 at 23:06
These `SELECT ... FOR UPDATE` queries do not block each other as you might expect, presumably because they do not match any rows. Manual testing indicates that they return immediately yet they both seem to hold an unknown lock (on the PK or its supremum, maybe? I don't see it, but it's there) that causes the blocking to defer until the first `INSERT`. Then 1 blocks until 2 tries to `INSERT`, and 2 immediately rolls back. — Michael - sqlbot, Dec 06 '16 at 02:19
Just to satisfy my curiosity, why is the `sleep(10)` unavoidable? I can't think of any such situation. — FDavidov, Dec 06 '16 at 08:32
@FDavidov We're using `tblFoo` as a table of pointers to data on the system. For every `id` there is corresponding bit of data that has been configured in a specific way. When a process requests data identified by an `id` that is not in the table, the process initializes the data, something that takes an indeterminate amount of time. I was hoping that the combination of `SELECT ... FOR UPDATE` and `INSERT` could be used to prevent any dirty reads and duplicate initialization. — MrDiggles, Dec 06 '16 at 15:54
I'm regret to say that your explanation does not help me to understand the `sleep` thing. The fact is: (1) You are generating a lock (SELECT FOR UPDATE), (2) wait 10 seconds, (3) Insert a row into the same table. I'm sorry to say, but this does not make any sense to me. Perhaps, I'm missing vital information. Who knows... — FDavidov, Dec 06 '16 at 16:51
@FDavidov Oh lol, sorry. The sleep statement is just a fill in for this prototype. In the actual implementation, the `sleep(10)` will be replaced with work that takes an arbitrary amount of time. I just wanted to see if I could get a proof of concept working. — MrDiggles, Dec 06 '16 at 17:38
I see. Well, all this comes down to having a scenario in which you are locking the same record (or set of records) from two independent transactions. The only thing I can say is to try and achieve a drastic performance improvement for these transactions reducing in this way to probability for these collisions to taka place. — FDavidov, Dec 06 '16 at 17:41

score 0 · Accepted Answer · answered Dec 05 '16 at 16:31

0

I think what is happening is, that you start 2 transactions. Both get a "select ... for update" on the same table. You would expect the second transaction to wait before the update. But from the docs https://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html it sounds like it will not (and this is the behaviour you see). So both "selects ... for update" block each other, leaving you with a deadlock.

answered Dec 05 '16 at 16:31

Seb

1,521
1
12
19

From looking at the docs, it looks like you're right. There doesn't seem to be a solution to my issue, which means I'll probably have to implement some kind of mutex table. Unless someone comes up with a clearer explanation (or a solution perhaps?), I will accept this answer. Thanks for the effort. – MrDiggles Dec 06 '16 at 22:07
1

@Seb SELECT FOR UPDATE acquires an exclusive lock. Only 1 transaction can acquire exclusive lock on a data at a time, preventing all other locks (including Shared) from other transactions. So deadlock is not a result of SFU. Document has clearly mentioned the deadlock is from the SELECT FOR SHARED statement from multiple transactions causing to wait for others to leave the SHARED lock for current transaction to get an exclusive lock for UPDATE. – Vyshnav Ramesh Thrissur Nov 22 '21 at 17:25

Multiple SELECT ... FOR UPDATE with a delayed INSERT INTO

1 Answers1