Description:
If one starts a transaction, update a table, then run LOCK TABLE and ALTER TABLE in two parallel connections subsequent update in the first connection fail with deadlock, then LOCK TABLE succeeds and table could be updated. See "How to repeat" also.
How to repeat:
--source include/have_innodb.inc
connect (addconroot1, localhost, root,,);
connect (addconroot2, localhost, root,,);
connection default;
create table t1(f1 int) engine=innodb;
create table t2(f1 int) engine=innodb;
insert into t1 values(2),(4),(6);
insert into t2 select * from t1;
insert into t2 values(2),(4);
select @@tx_isolation;
begin;
insert into t2 select * from t1;
select * from t2;
connection addconroot1;
--send lock table t2 write
connection addconroot2;
--sleep 1
--send alter table t1 engine=innodb
connection default;
--sleep 1
--error 1213
update t1 set f1 = f1/2;
connection addconroot2;
--reap
connection addconroot1;
--reap
delete from t2 limit 5;
unlock tables;
connection default;
select * from t2;
Output:
=====mysql-5.6=====
=====trx_alter_table_locks=====
create table t1(f1 int) engine=innodb;
create table t2(f1 int) engine=innodb;
insert into t1 values(2),(4),(6);
insert into t2 select * from t1;
insert into t2 values(2),(4);
select @@tx_isolation;
@@tx_isolation
REPEATABLE-READ
begin;
insert into t2 select * from t1;
select * from t2;
f1
2
4
6
2
4
2
4
6
lock table t2 write;
alter table t1 engine=innodb;
update t1 set f1 = f1/2;
ERROR 40001: Deadlock found when trying to get lock; try restarting transaction
delete from t2 limit 5;
unlock tables;
select * from t2;
f1
Suggested fix:
Do not release MDL after deadlock or rollback transaction or do not allow transaction to make new changes to the same table.
