Preliminary understanding of MySQLmetadatalock (MDL)

Overview

MDL means that once the DDL is blocked, all queries for the table will be suspended, including Select, but 5.6 has been improved, and 5.5 can be controlled by parameters.

If no MDL

Session 1: mysql> select version (); + ------------ + | version () | + ------------ + | 5.1.72-log | + ------------ + 1 row in set (0.00 sec) mysql> select @ tx_isolation; + ----------------- + | @ tx_isolation | + ------------------- + | REPEATABLE-READ | + ----------------- + 1 row in set (0.00 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t where id = 1; + ---- + -------- + | id | name | + ---- + -------- + | 1 | python | + ---- + -------- + 1 row in set (0.04 sec) Session 2: mysql> alter table t add column comment varchar (200) default 'I use python'; Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 Session 1: mysql> select * from t where id = 1; Empty set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.00 sec) mysql> begin; query OK, 0 rows affected (0.00 sec) mysql> select * from t where id = 1; + ---- + -------- + -------------- + | id | name | comment | + ---- + -------- + -------------- + | 1 | python | I use Python | + ---- + -------- + ------------ + 1 row in set (0.00 sec)

Different from the above, in 5.5 MDL, the length of life is extended, and the transaction is dead together. As long as the transaction is still there, MDL is there, because the transaction holds the MDL lock, any DDL operation takes a break during the transaction. The following is an example.

Session 1: mysql> select version (); + ------------ + | version () | + ------------ + | 5.5.16-log | + ------------ + 1 row in set (0.01 sec) mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from t order by id; + ---- + ------ + | id | name | + ---- + ------ + | 1 | a | 2 | e | 3 | c | + ---- + ------ + 3 rows in set (0.00 sec) session 2: mysql> alter table t add column cc char (10) default 'C lang '; <<== Hangs Session 3: mysql> show processlist; + ---- + ------ + ----------- + ------ + --------- + ------ + --------------------------------- + response + | Id | User | Host | db | Command | Time | State | + ---- + ------ + ----------- + ------ + --------- + ------ + ------------------------------- + bytes + | 2 | root | localhost | db1 | Sleep | 191 | NULL | 3 | root | localhost | db1 | Query | 125 | waiting for table metadata lock | alter table t add column cc char (10) default 'C lang '| 4 | root | localhost | NULL | Query | 0 | NULL | show processlist | + ---- + ------ + ----------- + ------ + --------- + ------ + --------------------------------- + response ------------------------------------------------------- +

mysql> show profiles;+----------+---------------+-------------------------------------------------------+| Query_ID | Duration      | Query                                                 |+----------+---------------+-------------------------------------------------------+|        1 | 1263.64100500 | alter table t add column dd char(10) default ' Elang' |+----------+---------------+-------------------------------------------------------+1 row in set (0.00 sec)mysql> show profile for query 1;+------------------------------+------------+| Status                       | Duration   |+------------------------------+------------+| starting                     |   0.000124 || checking permissions         |   0.000015 || checking permissions         |   0.000010 || init                         |   0.000023 || Opening tables               |   0.000063 || System lock                  |   0.000068 || setup                        |   0.000082 || creating table               |   0.034159 || After create                 |   0.000185 || copy to tmp table            |   0.000309 || rename result table          | 999.999999 || end                          |   0.004457 || Waiting for query cache lock |   0.000024 || end                          |   0.000029 || query end                    |   0.000009 || closing tables               |   0.000030 || freeing items                |   0.000518 || cleaning up                  |   0.000015 |+------------------------------+------------+18 rows in set (0.00 sec)

Case

Monitoring

Lock_wait_timeout

mysql> show variables like 'lock_wait_timeout';+-------------------+----------+| Variable_name     | Value    |+-------------------+----------+| lock_wait_timeout | 31536000 |+-------------------+----------+1 row in set (0.00 sec)

This variable specifies the timeout in seconds for attempts to acquire metadata locks. The permissible values range from 1 to 31536000 (1 year). The default is 31536000

Diagnosis

Connection #1:create table t1 (id int) engine=myisam;set @@autocommit=0;select * from t1;Connection #2:alter table t1 rename to t2; <-- Hangs 

For InnoDB tables:

create table t3 (id int) engine=innodb;create table t4 (id int) engine=innodb;delimiter |CREATE TRIGGER t3_trigger AFTER INSERT ON t3  FOR EACH ROW BEGIN    INSERT INTO t4 SET id = NEW.id;  END;|delimiter ;

Connection #1:begin;insert into t3 values (1);

Connection #2:drop trigger if exists t3_trigger; <-- Hangsmysql> SHOW ENGINE INNODB STATUS\G;............------------TRANSACTIONS------------Trx id counter BF03Purge done for trx's n:o < BD03 undo n:o < 0History list length 82LIST OF TRANSACTIONS FOR EACH SESSION:---TRANSACTION 0, not startedMySQL thread id 4, OS thread handle 0xa7d3fb90, query id 40 localhost rootshow engine innodb status---TRANSACTION BF02, ACTIVE 38 sec2 lock struct(s), heap size 320, 0 row lock(s), undo log entries 2MySQL thread id 2, OS thread handle 0xa7da1b90, query id 37 localhost root.........

TRANSACTIONSIf this section reports lock waits, your applications might have lock contention. The output can also help to trace the reasons for transaction deadlocks.

SELECT * FROM INNODB_LOCK_WAITS
SELECT * FROM INNODB_LOCKS WHERE LOCK_TRX_ID IN (SELECT BLOCKING_TRX_ID FROM INNODB_LOCK_WAITS)
SELECT INNODB_LOCKS. * FROM INNODB_LOCKS JOIN INNODB_LOCK_WAITS ON (INNODB_LOCKS.LOCK_TRX_ID = INNODB_LOCK_WAITS.BLOCKING_TRX_ID)
SELECT * FROM INNODB_LOCKS WHERE LOCK_TABLE = db_name.table_name
SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY FROM INNODB_TRX WHERE TRX_STATE = 'lock wait'

Relationship with table cache

Session 1: mysql> show status like 'open % tables '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Open_tables | 26 | <== number of currently opened tables | Opened_tables | 2 | <== opened tables quantity + --------------- + ------- + 2 rows in set (0.00 sec) session 2: mysql> alter table t add column Oxx char (20) default 'oracle '; Query OK, 3 rows affected (0.05 sec) Records: 3 Duplicates: 0 Warnings: 0 Session 1: mysql> select * from t order by id; + ---- + ------ + -------- + --------- + ------- + -------- + | id | name | cc | dd | EE | ff | OO | OE | OF | OX | Oxx | + ---- + ------ + -------- + --------- + ------- + -------- + | 1 | a | c lang | Elang | Golang | Golang | MySQL | ORACLE | 2 | e | c lang | Elang | Golang | MySQL | ORACLE | 3 | c | c lang | Elang | Golang | MySQL | ORACLE | + ---- + ------ + -------- + --------- + ------- + -------- + -------- + 3 rows in set (0.00 sec) mysql> show status like 'open % tables '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Open_tables | 27 | Opened_tables | 3 | + --------------- + ------- + 2 rows in set (0.00 sec) session 2: mysql> alter table t add column Oxf char (20) default 'oracle '; Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0 Session 1: mysql> show status like 'open % tables '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | Open_tables | 26 | Opened_tables | 3 | + --------------- + ------- + 2 rows in set (0.00 sec)

Conclusion:

When you need to DDL "Hot tables", you need to be especially careful. Otherwise, it will easily cause MDL wait, resulting in connection depletion or Server suspension.