Bug #73666 DEADLOCK WHEN DDL UNDER LOCK TABLES WRITE, READ LOCAL + PREPARE.
Submitted: 21 Aug 2014 9:09 Modified: 21 Aug 2014 9:09
Reporter: Dmitry Lenev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5.37-bzr, 5.6.21-bzr, 5.7.5-bzr OS:Any
Assigned to: CPU Architecture:Any

[21 Aug 2014 9:09] Dmitry Lenev
Description:
This is a more restricted and exotic case of bug #57474 which was not fixed by implementation of WL#6671.

A deadlock can occur under the following (rare) conditions:
- A DDL statement like ALTER or DROP table is executed under LOCK TABLES
- This LOCK TABLES statement in addition to table to be changed by DDL also
has some MyISAM (important!) table locked in READ LOCAL (important!) mode
- There is some concurrent DML/transaction which is blocked on table-level thr_lock.c lock held by LOCK TABLES READ LOCAL. This DML/transaction also
uses some other tables.
- There is a concurrent PREPARE (or corresponding C/... API call) which
prepares statement using tables to be affected by the first DDL and some
other table which is affected by another DDL statement which has to wait for
the DML statement/transaction.

Some parts of this deadlock involve waiting on thr_lock.c locks so they are
not visible to deadlock detector of MDL subsystem and it cannot be properly
detected by it.

Please note that this bug report is about the specific situation described
above. Other deadlocks possibly involving thr_lock.c locks/intersubsystem 
deadlocks are not subject of this bug and should be reported separately.

How to repeat:
connect (con1,localhost,root,,test,,);
connect (con2,localhost,root,,test,,);
connect (con3,localhost,root,,test,,);
connection default;

create table t1(i int);
create table t2(i int);
create table t3(i int) engine=myisam;
create table t4(i int);

lock tables t1 write, t3 read local;

connection con1;
begin;
select count(*) from t4;
--send update t3 set i = 1;

connection con2;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table level lock" and
        info = "update t3 set i = 1";
--source include/wait_condition.inc

--send rename table t2 to t0, t4 to t2, t0 to t4;

connection con3;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table metadata lock" and
        info = "rename table t2 to t0, t4 to t2, t0 to t4";
--source include/wait_condition.inc

--send prepare stmt1 from 'select * from t1, t2';

connection default;
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Waiting for table metadata lock" and
        info = "select * from t1, t2";
--source include/wait_condition.inc

alter table t1 add column j int;
[21 Aug 2014 14:16] Laurynas Biveinis
"Other deadlocks possibly involving thr_lock.c locks/intersubsystem 
deadlocks are not subject of this bug and should be reported separately."

- such as bug 71433 for example?
[21 Aug 2014 19:12] Dmitry Lenev
Hello Laurynas!

Yes, bug #71433 is different/separate issue, to be fixed separately.