初步认知MySQLmetadatalock(MDL) - 数据库编程

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)

案例

监控

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

诊断

Connection #1:

create table t1 (id int) engine=myisam;
set @@autocommit=0;
select * from t1;
Connection #2:

alter table t1 rename to t2; <-- Hangs

对于InnoDB表：

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; <-- Hangs

mysql> SHOW ENGINE INNODB STATUS\G;
....
....
....
------------
TRANSACTIONS
------------
Trx id counter BF03
Purge done for trx's n:o < BD03 undo n:o < 0
History list length 82
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 4, OS thread handle 0xa7d3fb90, query id 40 localhost root
show engine innodb status
---TRANSACTION BF02, ACTIVE 38 sec
2 lock struct(s), heap size 320, 0 row lock(s), undo log entries 2
MySQL thread id 2, OS thread handle 0xa7da1b90, query id 37 localhost root

...
...
...

TRANSACTIONS

If 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'

与table cache的关系

会话1：

mysql> show status like 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 26    |  <==当前打开的表数量
| Opened_tables | 2     |  <==已经打开的表数量
+---------------+-------+
2 rows in set (0.00 sec)

会话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

会话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 | ORACLE | ORACLE | ORACLE |
|  2 | e    | c lang |  Elang |  Golang |  Golang | MySQL | ORACLE | ORACLE | ORACLE | ORACLE |
|  3 | c    | c lang |  Elang |  Golang |  Golang | MySQL | ORACLE | ORACLE | ORACLE | 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)

会话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

会话1：

mysql> show status like 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------

初步认知MySQLmetadatalock(MDL)(二)