|
According to documentation, in the test case below, the table t2 should be implicitly locked with a WRITE lock.
However, when the ALTER statement (or other DDL on t2) is issued, it returns the error:
ERROR 1099 (HY000): Table 't2' was locked with a READ lock and can't be updated
|
According to metadata_lock_info, the following locks are taken:
+-----------+--------------------------+---------------+----------------------+--------------+------------+
|
| THREAD_ID | LOCK_MODE | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
|
+-----------+--------------------------+---------------+----------------------+--------------+------------+
|
| 10 | MDL_INTENTION_EXCLUSIVE | NULL | Global read lock | | |
|
| 10 | MDL_SHARED_NO_READ_WRITE | NULL | Table metadata lock | test | t1 |
|
| 10 | MDL_SHARED_WRITE | NULL | Table metadata lock | test | t2 |
|
| 10 | MDL_INTENTION_EXCLUSIVE | NULL | Schema metadata lock | test | |
|
+-----------+--------------------------+---------------+----------------------+--------------+------------+
|
MySQL used to behave the same way, but in 5.7 it has changed. No error anymore, and it takes SHARED_NO_READ_WRITE instead of MDL_SHARED_WRITE:
GLOBAL NULL NULL 140009223037808 INTENTION_EXCLUSIVE STATEMENT GRANTED sql_base.cc:5495 23 4664
|
SCHEMA test NULL 140009223035952 INTENTION_EXCLUSIVE TRANSACTION GRANTED sql_base.cc:5480 23 4664
|
TABLE test t1 140009223056544 SHARED_NO_READ_WRITE TRANSACTION GRANTED sql_parse.cc:5978 23 4666
|
TABLE test t2 140009223032576 SHARED_NO_READ_WRITE TRANSACTION GRANTED table.h:1744 23 4874
|
|
Test case
|
create table t1 (i int);
|
create table t2 (i int);
|
create trigger tr1 after insert on t1 for each row insert into t2 (i) values (1);
|
lock table t1 write;
|
|
alter table t2 add j int;
|
|