4

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
  • 1
    This is probably caused by InnoDB's "gap locking": dev.mysql.com/doc/refman/5.7/en/… – a_horse_with_no_name Nov 2 '16 at 9:21
  • Based on my reading of "gap locking" it seems it should not lock since my where clause should not be inclusive of "after the last index record". However, it does seem that using READ COMMITTED on Transaction 1 fixes the issue. – ColinM Nov 2 '16 at 15:47
1

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.