绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
简单总结Innodb定位死锁类故障常用的技术手段
2019-05-01 16:29:03

第一部分、问题说明

我们经常会碰到这样的情况,某个事务执行完了未提交,后续再来一个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


分享好友

分享这个小栈给你的朋友们,一起进步吧。

MySQL干货资料
创建时间:2020-05-06 14:18:32
每天都有干货输出哦
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 飘絮絮絮丶
    专家
戳我,来吐槽~