mysql锁等待分析--李晓蒙
1、简单说明
使用innodb存储引擎后,mysql有三张表来分析锁及阻塞的问题,在information_schema下面有三张表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS,通过这三张表,可以更简单地监控当前的事务并分析可能存在的问题。
mysql> show tables like '%INNODB%';
+-----------------------------------------+
| Tables_in_information_schema (%INNODB%) |
+-----------------------------------------+
| INNODB_LOCKS |
| INNODB_TRX |
| INNODB_LOCK_WAITS |
INNODB_TRX表及结构
比较常用的列:
trx_id:InnoDB存储引擎内部唯一的事物ID
trx_status:当前事务的状态
trx_requested_lock_id:等待事务的锁ID
trx_wait_started:事务等待的开始时间
trx_weight:事务的权重,反应一个事务修改和锁定的行数,当发现死锁需要回滚时,权重越小的值被回滚
trx_mysql_thread_id:MySQL中的进程ID,与show processlist中的ID值相对应
trx_query:事务运行的SQL语句
其余两个表字段相对较少
INNODB_LOCKS
INNODB_LOCK_WAITS
2、锁定测试
mysql> use test;
Database changed
mysql> create table mytest1 (id int(4),pername char(10),bithday date,telphone char(11));
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| mytest1 |
+----------------+
1 row in set, 1 warning (0.00 sec)
--以mytest1表进行测试,里面的记录如下:
mysql> select * from mytest1;
+------+---------+------------+----------+
| id | pername | bithday | telphone |
+------+---------+------------+----------+
| 1 | Jone | 1994-01-02 | 11111111 |
| 2 | Tom | 1994-04-23 | 11214115 |
| 3 | Rose | 1993-05-02 | 21214719 |
| 4 | Jack | 1992-07-18 | 41218613 |
| 5 | Block | 1991-09-21 | 75294651 |
| 6 | Block | 1990-10-21 | 65364671 |
+------+---------+------------+----------+
6 rows in set (0.00 sec)
--将自动提交改为手动提交
mysql> show variables like '%commit%';
+--------------------------------+-------+
| Variable_na