Bug #82204 Metadata lock does not protect transaction if LOCK TABLE and ALTER involved
Submitted: 12 Jul 2016 19:46 Modified: 13 Jul 2016 7:11
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S2 (Serious)
Version:5.5.50, 5.6.31, 5.7.13 OS:Any
Assigned to: CPU Architecture:Any

[12 Jul 2016 19:46] Sveta Smirnova
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.
[13 Jul 2016 7:11] Umesh Shastry
Hello Sveta,

Thank you for the report and test case.
Verified as described with 5.5.50 build.

Regards,
Umesh