锁信息查看

锁信息：
 在information_schema下面有三张表:INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通过这三张表，可以更简单地监控当前的事务并分析可能存在的问题。




先看当前锁表情况
show open TABLES where In_use > 0;
show global status like 'table_locks%';
show open TABLES where In_use > 0;
show open tables from XXX(数据库名);//查看数据库哪些表正在使用In_use 以及锁定Name_locked
show global status like 'table_locks%';
//Table_locks_immediate 发生表锁定操作, 但表锁定后马上释放
//Table_locks_waited  发生表锁定, 并因此具有锁等待




INNODB_TRX表及结构
trx_id:InnoDB存储引擎内部唯一的事物ID
trx_status:当前事务的状态
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表结构信息：


 desc innodb_locks;
+-------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| lock_id | varchar(81) | NO | | | |#锁ID
| lock_trx_id | varchar(18) | NO | | | |#拥有锁的事务ID
| lock_mode | varchar(32) | NO | | | |#锁模式
| lock_type | varchar(32) | NO | | | |#锁类型
| lock_table | varchar(1024) | NO | | | |#被锁的表
| lock_index | varchar(1024) | YES | | NULL | |#被锁的索引
| lock_space | bigint(21) unsigned | YES | | NULL | |#被锁的表空间号
| lock_page | bigint(21) unsigned | YES | | NULL | |#被锁的页号
| lock_rec | bigint(21) unsigned | YES | | NULL | |#被锁的记录号
| lock_data | varchar(8192) | YES | | NULL | |#被锁的数据






desc innodb_lock_waits;
+-------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+-------------+------+-----+---------+-------+
| requesting_trx_id | varchar(18) | NO | | | |#请求锁的事务ID
| requested_lock_id | varchar(81) | NO | | | |#请求锁的锁ID
| blocking_trx_id | varchar(18) | NO | | | |#当前拥有锁的事务ID
| blocking_lock_id | varchar(81) | NO | | | |#当前拥有锁的锁ID
+-------------------+-------------+------+-----+---------+-------+


-------------------查看引起锁的源头
#mysql 锁之间的依赖关系【打开注释部分是：查找锁最源头的线程id之间的关系】
SELECT distinct b.trx_id blocking_trx_id,
                b.trx_mysql_thread_id,
                SUBSTRING(p. HOST, 1, INSTR(p. HOST, ':') - 1) blocking_host,
                SUBSTRING(p. HOST, INSTR(p. HOST, ':') + 1) blocking_port,
                IF(p.COMMAND = 'Sleep', p.TIME, 0) idel_in_trx,
                b.trx_query blocking_query,
                r.trx_id waiting_trx_id,
                r.trx_mysql_thread_id waiting_thread,
                TIMESTAMPDIFF(SECOND, r.trx_wait_started, CURRENT_TIMESTAMP) wait_time,
                r.trx_query waiting_query,
                l.lock_table waiting_table_lock
  FROM information_schema.INNODB_LOCKS l
  LEFT JOIN information_schema.INNODB_LOCK_WAITS w
    ON w.requested_lock_id = l.lock_id
  LEFT JOIN information_schema.INNODB_TRX b
    ON b.trx_id = w.blocking_trx_id
  LEFT JOIN information_schema.INNODB_TRX r   
    ON r.trx_id = w.requesting_trx_id
  LEFT JOIN information_schema. PROCESSLIST p
    ON p.ID = b.trx_mysql_thread_id
JOIN (SELECT blocking_trx_id 
      FROM information_schema.INNODB_LOCK_WAITS ilw
     WHERE blocking_trx_id NOT IN
           (SELECT requesting_trx_id
              FROM information_schema.INNODB_LOCK_WAITS)) c
ON c.blocking_trx_id = b.trx_id 
ORDER BY wait_time DESC;






 锁等待和持有锁的相互关系：
 
 SELECT * FROM INNODB_LOCK_WAITS\G;


requesting_trx_id: B15
requested_lock_id: B15:0:32777:2
  blocking_trx_id: B14
 blocking_lock_id: B14:0:32777:2
1 row in set (0.03 sec)
ERROR: 
No query specified
通过视图INNODB_LOCK_WAITS可以清晰的看到B14持有锁，而B15处于锁等待；
 
3.4 锁等待的原因
mysql> SELECT * FROM INNODB_LOCKS\G;
*************************** 1. row ***************************
    lock_id: B15:0:32777:2
lock_trx_id: B15
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`john`
 lock_index: `GEN_CLUST_INDEX`
 lock_space: 0
  lock_page: 32777
   lock_rec: 2
  lock_data: 0x000000640000
*************************** 2. row ***************************
    lock_id: B14:0:32777:2
lock_trx_id: B14
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`john`
 lock_index: `GEN_CLUST_INDEX`
 lock_space: 0
  lock_page: 32777
   lock_rec: 2
  lock_data: 0x000000640000
2 rows in set (0.01 sec)


#下面查询显示存储引擎层有多少查询被哪些线程阻塞
SELECT CONCAT('thread ', b.trx_mysql_thread_id, ' from ',p.host) AS who_blocks, IF (p.command = "Sleep",p.time, 0) AS idle_in_trx, MAX(TIMESTAMPDIFF(SECOND,r.trx_wait_started, NOW())) AS max_wait_time, COUNT(*) AS num_waiters
FROM information_schema.innodb_lock_waits AS w
INNER JOIN information_schema.innodb_trx AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx AS r ON r.trx_id = w.requesting_trx_id
LEFT JOIN information_schema.processlist AS p ON p.id = b.trx_mysql_thread_id
GROUP BY who_blocks
ORDER BY num_waiters DESC;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30162734/viewspace-2150174/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30162734/viewspace-2150174/