Mysql – Why does MySQL UPDATE with <= lock insert intention

deadlockinnodbMySQLmysql-5.7transaction

I'd like to be able to update a table without locking inserts to avoid possible deadlocks. I thought I could use a query before the update to limit it so that the insert gap is not locked but it doesn't seem to work:

CREATE TABLE `foo` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `value` varchar(45) NOT NULL
) ENGINE='InnoDB';
INSERT INTO foo (value) VALUES ('foo1');
INSERT INTO foo (value) VALUES ('foo2');
INSERT INTO foo (value) VALUES ('foo3');

Transaction 1

start transaction;
select MAX(id) INTO @max FROM foo;
update foo set value = NOW() where id <= @max;

Transaction 2

start transaction;
insert into foo (value) values ('foobar');
-- waiting ...

Now transaction 2 is waiting for transaction 1 which I didn't expect.

My theory works if I subtract 1 from MAX:

Transaction 1

start transaction;
select MAX(id) - 1 INTO @max FROM foo;
update foo set value = NOW() where id <= @max;

Transaction 2

start transaction;
insert into foo (value) values ('foobar');
-- Query OK, 1 row affected

So why doesn't the first example work and is there any way to accomplish the update all the way up to MAX(id) without blocking inserts using a regular auto-increment primary key?

Tested using MySQL 5.7. Below is the SHOW ENGINE INNODB STATUS\G output for the first case (Transaction 2 blocked):

------------
TRANSACTIONS
------------
Trx id counter 6214932
Purge done for trx's n:o < 6214930 undo n:o < 0 state: running but idle
History list length 1836
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422057928858336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 6214931, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 369, OS thread handle 140582776452864, query id 7104 localhost root update
insert into foo (value) values ('foobar')
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4135 page no 3 n bits 80 index PRIMARY of table `test`.`foo` trx id 6214931 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

------------------
---TRANSACTION 6214930, ACTIVE 15 sec
2 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 5
MySQL thread id 367, OS thread handle 140582775654144, query id 7102 localhost root cleaning up
Trx read view will not see trx with id >= 6214930, sees < 6214930

Best Answer

I believe the issue is due to the UPDATE using "next-key locking" as evidenced by "asc supremum" in the engine status conflicting with "insert intention locking" of the INSERT as evidenced by "lock_mode X insert intention waiting" in the engine status. It seems the only way around this is to effectively disable gap locking entirely by using READ COMMITTED transaction isolation level for Transaction 1 or innodb_locks_unsafe_for_binlog.

Transaction 1

set transaction isolation level read committed;
start transaction;
update foo set value = NOW();

Transaction 2

start transaction;
insert into foo (value) values ('foobar');
-- Query OK, 1 row affected

Since using READ COMMITTED disables all gap locking (including next-key locking) in favor of index record locking it is not necessary to use where id <= @max so this can now be removed. I do not believe it is possible to accomplish this update without blocking the insert using REPEATABLE READ.

Related Question