查看Mysql正在执行的事务、锁、等待和锁日志

作者: 10xjzheng | 来源:发表于2018-12-10 17:38 被阅读32次

查看Mysql正在执行的事务、锁、等待和锁日志
事务查看
(4)头条mysql
mysql中的锁
浅析mysql的锁
锁-0
高性能Mysql笔记
mysql提示 Lock wait timeout exceed
MySQL查看事务锁.sql
mysql 查看死锁

1. show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-12-10 17:11:35 0x7f3263cae700
*** (1) TRANSACTION:
TRANSACTION 14340, ACTIVE 40 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 139854399600384, query id 35 58.250.203.218 root updating
UPDATE xg_order0 SET mobile='1379411888' where order_no='20180325133505884'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 344 index idx_order_no of table `order`.`xg_order0` trx id 14340 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 17; hex 3230313830333235313333353035383834; asc 20180325133505884;;
 1: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 14341, ACTIVE 36 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 139854399334144, query id 46 58.250.203.218 root updating
UPDATE xg_order0 SET mobile='13794117555' where order_no='20180325133531350'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 55 page no 4 n bits 344 index idx_order_no of table `order`.`xg_order0` trx id 14341 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 17; hex 3230313830333235313333353035383834; asc 20180325133505884;;
 1: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 55 page no 4 n bits 344 index idx_order_no of table `order`.`xg_order0` trx id 14341 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 17; hex 3230313830333235313333353331333530; asc 20180325133531350;;
 1: len 4; hex 80000002; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 14344
Purge done for trx's n:o < 14344 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421329964514928, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421329964514016, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421329964513104, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
============================

事务1

image.png
事务2

image.png

2. show status like 'innodb_row_lock_%';

+-------------------------------+-----------+
| Variable_name                 | Value     |
+-------------------------------+-----------+
| Innodb_row_lock_current_waits | 2         |
| Innodb_row_lock_time          | 334377476 |
| Innodb_row_lock_time_avg      | 50678     |
| Innodb_row_lock_time_max      | 51974     |
| Innodb_row_lock_waits         | 6598      |
+-------------------------------+-----------+
解释如下：
Innodb_row_lock_current_waits : 当前等待锁的数量
Innodb_row_lock_time : 系统启动到现在，锁定的总时间长度
Innodb_row_lock_time_avg : 每次平均锁定的时间
Innodb_row_lock_time_max : 最长一次锁定时间
Innodb_row_lock_waits : 系统启动到现在总共锁定的次数

3. information_schema库中的三个关于锁的表

innodb_trx ## 当前运行的所有事务
innodb_locks ## 当前出现的锁
innodb_lock_waits ## 锁等待的对应关系

desc information_schema.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 information_schema.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
+-------------------+-------------+------+-----+---------+-------+

desc information_schema.innodb_trx;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id | varchar(18) | NO | | | |#事务ID
| trx_state | varchar(13) | NO | | | |#事务状态：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 ||#事务开始时间；
| trx_requested_lock_id | varchar(81) | YES | | NULL ||#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事务开始等待的时间
| trx_weight | bigint(21) unsigned | NO | | 0 | |#
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 ||#事务线程ID
| trx_query | varchar(1024) | YES | | NULL | |#具体SQL语句
| trx_operation_state | varchar(64) | YES | | NULL ||#事务当前操作状态
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 ||#事务中有多少个表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 ||#事务拥有多少个锁
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 ||#事务锁住的内存大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 ||#事务锁住的行数
| trx_rows_modified | bigint(21) unsigned | NO | | 0 ||#事务更改的行数
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 ||#事务并发票数
| trx_isolation_level | varchar(16) | NO | | | |#事务隔离级别
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性检查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外键检查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL ||#最后的外键错误
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 ||#
+----------------------------+---------------------+------+-----+---------------------+-------+