部分、问题说明
我们经常会碰到这样的情况，某个事务执行完了未提交，后续再来一个DDL和DML操作，导致后面的session要么处于waiting for metadata lock，要么是锁等待超时。这时我们往往只能找
到这个未提交的事务的事务id和session id，但是一般都处于sleep状态，不好分析事务内容到底是什么。
第二部分、用innodb_lock_monitor+show engine innodb status定位执行完但未提交的源头SQL
一、启用 innodb_monitor
mysqld在线运行时，创建 innodb_monitor 表，即可记录相关信息到日志文件
mysql> create table innodb_monitor ( id int ) engine = innodb;
相关的信息就会输出到 .err 日志文件里了。
二、如果有死锁发生，会有相关的记录
------------------------
LATEST DETECTED DEADLOCK
------------------------
160128 1:51:53 #这里显示了近一次发生死锁的日期和时间
1、下面这部分显示的是死锁的个事务的信息：
*** (1) TRANSACTION:
TRANSACTION D20847, ACTIVE 141 sec starting index read：
这行表示事务D20847，ACTIVE 141 sec表示事务处于活跃状态141s，starting index read表示正在使用索引读取数据行
mysql tables in use 1, locked 1
#这行表示事务D20847正在使用1个表，且涉及锁的表有1个
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s)
#这行表示在等待3把锁，占用内存376字节，涉及2行记录
MySQL thread id 20027, OS thread handle 0x7f0a4c0f8700, query id 1818124 localhost root statistics
#这行表示该事务的线程ID信息，操作系统句柄信息，连接来源、用户
select * from test_deadlock where id=2 for update #这行表示事务涉及的SQL
2、下面这一部分显示的是当死锁发生时，个事务正在等待的锁等信息：
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
#这行信息表示个事务正在等待锁被授予
RECORD LOCKS space id 441 page no 3 n bits 72 index `PRIMARY` of table `xiaoboluo`.`test_deadlock` trx id D20847 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
RECORD LOCKS space id 441 page no 3 n bits 72 index `PRIMARY` of table `xiaoboluo`.`test_deadlock` trx id D20847 lock_mode X locks rec but not gap waiting
#这行信息表示等待的锁是一个record lock，空间id是441，页编号为3，大概位置在页的72位处，锁发生在表xiaoboluo.test_deadlock的主键上，是一个X锁，但是不是gap lock。 waiting表
示正在等待锁
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 #这行表示record lock的heap no 位置
3、下面这部分是事务二的状态：
*** (2) TRANSACTION:
TRANSACTION D20853, ACTIVE 119 sec starting index read #事务2处于活跃状态119s
mysql tables in use 1, locked 1 #正在使用1个表，涉及锁的表有1个
3 lock struct(s), heap size 1248, 2 row lock(s) #涉及3把锁，2行记录
MySQL thread id 20081, OS thread handle 0x7f0a0f020700, query id 1818204 localhost root statistics
select * from test_deadlock where id=1 for update #第二个事务的SQL
第三部分、方案2-用information_schema字典表定位执行完但未提交的源头SQL
一、主要利用information_schema以下字典表进行分析：
1、innodb_trx ## 当前运行的所有事务
2、innodb_locks ## 当前出现的锁
3、innodb_lock_waits ## 锁等待的对应关系
4、processlist #线程清单
5、events_statements_current
6、threads
二、建立测试数据：
use test;
create table tx1
(id int primary key ,
c1 varchar(20),
c2 varchar(30))
engine=innodb default charset = utf8 ;
insert into tx1 values
(1,’aaaa’,'aaaaa2′),
(2,’bbbb’,'bbbbb2′),
(3,’cccc’,'ccccc2′);
commit;
三、产生事务
### Session1
mysql> showvariables like '%innodb_lock_w%';
+--------------------------+-------+
|innodb_lock_wait_timeout | 50 | # INNODB默认锁等待的超时时间五十秒。
+--------------------------+-------+
start transaction;
update tx1 set c1=’heyf’,c2=’heyf’ where id = 3;
### 产生事务，在 innodb_trx 就有数据
information_schema > select * from innodb_trx \G
*************************** 1. row ***************************
trx_id: 3669D82
trx_state: RUNNING
trx_started: 2010-12-24 13:38:06
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 2344
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 1
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
### 由于没有产生锁等待，下面两个表没有数据
information_schema > select * from innodb_lock_waits \G
information_schema > select * from innodb_locks \G
四、产生锁等待
#### session 2
start transaction;
update tx1 set c1=’heyfffff’,c2=’heyffffff’ where id =3 ;
root@127.0.0.1 : information_schema 13:39:01> select * from innodb_trx \G
*************************** 1. row ***************************
trx_id: 3669D83 ##第2个事务
trx_state: LOCK WAIT ## 处于等待状态
trx_started: 2010-12-24 13:40:07
trx_requested_lock_id: 3669D83:49:3:4 ##请求的锁ID
trx_wait_started: 2010-12-24 13:40:07
trx_weight: 2
trx_mysql_thread_id: 2346 ##线程 ID
trx_query: update tx1 set c1=’heyfffff’,c2=’heyffffff’ where id =3
trx_operation_state: starting index read
trx_tables_in_use: 1 ##需要用到1个表
trx_tables_locked: 1 ##有1个表被锁
*************************** 2. row ***************************
trx_id: 3669D82 ##第1个事务
trx_state: RUNNING
trx_started: 2010-12-24 13:38:06
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 2344
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
information_schema> select * from innodb_locks \G
*************************** 1. row ***************************
lock_id: 3669D83:49:3:4 ## 第2个事务需要的锁
lock_trx_id: 3669D83
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tx1`
lock_index: `PRIMARY`
lock_space: 49
lock_page: 3
lock_rec: 4
lock_data: 3
*************************** 2. row ***************************
lock_id: 3669D82:49:3:4 ## 第1个事务需要的锁
lock_trx_id: 3669D82
lock_mode: X
lock_type: RECORD
lock_table: `test`.`tx1`
lock_index: `PRIMARY`
lock_space: 49
lock_page: 3
lock_rec: 4
lock_data: 3
information_schema > select * from innodb_lock_waits \G
*************************** 1. row ***************************
requesting_trx_id: 3669D83 ## 请求锁的事务
requested_lock_id: 3669D83:49:3:4 ## 请求锁的锁ID
blocking_trx_id: 3669D82 ## 拥有锁的事务
blocking_lock_id: 3669D82:49:3:4 ## 拥有锁的锁ID
1 row in set (0.00 sec)
mysql> select* from performance_schema.events_statements_current\G
***************************1. row ***************************
...
SQL_TEXT: update tx1 set c1=’heyf’,c2=’heyf’ where id = 3; # 找到了一个一直没有提交的事务
不过方案1有个缺陷，一个事务可能有一组sql组成，这个方法只能看到这个事务后执行的是什么SQL，无法看到全部。
五、分析并准确kill
1、show processlist;
2、information_schema.processlist和events_statements_current一一对应起来，可以通过performance_schema.threads表来关联，给出一个可行的sql
select a.SQL_TEXT,c.id,d.trx_started
from events_statements_current a
join threads b on a.THREAD_ID=b.THREAD_ID
join information_schema.processlist c on b.PROCESSLIST_ID=c.id
join information_schema.innodb_trx d on c.id=d.trx_mysql_thread_id
order by d.trx_started \G
3、kill id
第四部分、数据库innodb锁和阻塞信息查看
一、查看下在锁的事务
SELECT b.,a.
FROM information_schema.INNODB_TRX a,information_schema.PROCESSLIST b
WHERE a.trx_mysql_thread_id=b.ID
AND a.trx_state=’RUNNING’ ;
select b.trx_mysql_thread_id as ‘被阻塞线程’,
b.trx_query as ‘被阻塞SQL’,
b.trx_mysql_thread_id as ‘阻塞线程’,
b.trx_query as ‘阻塞SQL’,
(UNIX_TIMESTAMP() - UNIX_TIMESTAMP(b.trx_started)) as ‘阻塞时间’
from
information_schema.innodb_lock_waits a
join information_schema.innodb_trx b
on a.requesting_trx_id=b.trx_id
join information_schema.innodb_trx c
on a.blocking_trx_id=c.trx_id
where (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started))>10;
+—————–+——————–+————–+———–+————–+
| 被阻塞线程 | 被阻塞SQL | 阻塞线程 | 阻塞SQL | 阻塞时间 |
+—————–+——————–+————–+———–+————–+
| 5 | update t1 set id=2 | 4 | NULL | 25 |
+—————–+——————–+————–+———–+————–+
二、杀死进程id（就是上面命令的trx_mysql_thread_id列）
kill 线程id
第五部分、调整锁请求超时时间忽略锁等待
一、调整锁请求超时时间
[mysqld]
log-error =/var/log/mysqld3306.log
innodb_lock_wait_timeout=30 #锁请求超时时间(秒)
innodb_rollback_on_timeout = 1 #事务中某个语句锁请求超时将回滚整个事务
二、重启服务
三、查看服务器配置参数。
show variables like ‘%timeout%’;
第六部分、可能用到的其它技术
一、粗略分析后紧急查杀
1、能够查有多少线程正在使用某张表
不能帮助解答：当前某张表是否有死锁，谁拥有表上的这个锁等。
show open tables;
show open tables where in_use > 0;
show open tables from database;
Name_locked表示表名是否被锁，这一般发生在Drop或Rename命令操作这张表时。
2、查询进程
show processlist; # 只列出前100条
show full processlist;
3、杀死进程id（就是上面命令的id列）
kill id
二、用general_log等着问题复现的方式来定位
mysql> show processlist;
+----+
| Id |
+----+
| 4 |
mysql> setglobal general_log=1;
mysql> select* from mysql.general_log where thread_id=4;
三、锁定状态
mysql> show global status like '%lock%';
Table_locks_waited/Table_locks_immediate=0.3% 如果这个比值比较大的话，说明表锁造成的阻塞比较严重
Innodb_row_lock_waits innodb行锁，太大可能是间隙锁造成的
四、假如后面应用层终commit了，那么会在binlog里记录，可以根据当时的session id去binlog查看
五、本文参网或引用了以下文章
https://www.cnblogs.com/kucha/articles/5015665.html
innodb_lock_monitor+show engine innodb status
https://blog.csdn.net/cug_jiang126com/article/details/52046014
https://blog.csdn.net/iprettydeveloper/article/details/53943939
https://www.cnblogs.com/yulibostu/articles/9799360.html
动力小刚于2019年4月 个人邮箱：zcs0237#163.com
