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
READ COMMITTED
on Transaction 1 fixes the issue.