首页 / MYSQL / MySQLDeadLock故障排查全过程记录

MySQLDeadLock故障排查全过程记录

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQLDeadLock故障排查全过程记录，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含8462字，纯文字阅读大概需要13分钟。

内容图文

【作者】

刘博：携程技术保障中心数据库高级经理，主要关注Sql server和Mysql的运维和故障处理。

【环境】

版本号：5.6.21

隔离级别：REPEATABLE READ

【问题描述】

接到监控报警，有一个线上的应用DeadLock报错，每15分钟会准时出现，报错统计如下图：

MySQLDeadLock故障排查全过程记录 - 文章图片

登录Mysql服务器查看日志：

mysql> show engine innodb status\G

*** (1) TRANSACTION:

TRANSACTION 102973, ACTIVE 11 sec starting index read

mysql tables in use 3, locked 3

LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s)

MySQL thread id 6, OS thread handle 140024996574976, query id 83 localhost us updating

UPDATE TestTable

SET column1 = 1,

Column2 = sysdate(),

Column3= '026'

Column4 = 0

AND column5 = 485

AND column6 = 'SEK'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_column6 of table test.TestTable trx id 102973 lock_mode X waiting

Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007e1452; asc ~ R;;

*** (2) TRANSACTION:

TRANSACTION 102972, ACTIVE 26 sec starting index read

mysql tables in use 3, locked 3

219 lock struct(s), heap size 24784, 2906 row lock(s), undo log entries 7

MySQL thread id 5, OS thread handle 140024996841216, query id 84 localhost us updating

UPDATE TestTable

SET Column1 = 1,

Column2 = sysdate(),

Column3 = '026'

Column4 = 0

AND Column5 = 485

AND Column6 = 'SEK'

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 417 page no 1493 n bits 1000 index idx_Column6 of table test.TestTable trx id 102972 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;


Record lock, heap no 859 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007e1452; asc ~ R;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 601 page no 89642 n bits 1000 index idx_column6 of table test.TestTable trx id 32231892482 lock_mode X locks rec but not gap waiting

Record lock, heap no 38 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eea14; asc ~ ;;

大致一看，更新同一索引的同一行，应该是一个Block，报TimeOut的错才对，怎么会报DeadLock?

【初步分析】

先分析下(2) TRANSACTION，TRANSACTION 32231892482。

等待的锁信息为：

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eea14; asc

持有的锁信息为：

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eeac4; asc

再先分析下(1) TRANSACTION，TRANSACTION 32231892617。

等待的锁信息为：

0: len 3; hex 53454b; asc SEK;;

1: len 8; hex 80000000007eeac4; asc

于是可以画出的死锁表，两个资源相互依赖，造成死锁：

TRANSACTION	Hold	Wait
32231892617	53454b\80000000007eea14	53454b\80000000007eeac4
32231892482	53454b\80000000007eeac4	53454b\80000000007eea14

让我们再看一下explain结果：

mysql>desc UPDATE TestTable SET Column1=1, Column2 = sysdate(),Column3 = '025' Column4 = 0 AND Column5 = 477 AND Column6 = 'SEK' \G;

*************************** 1. row ***************************

id: 1

select_type: UPDATE

table: TestTable

partitions: NULL

type: index_merge

possible_keys: column5_index,idx_column5_column6_Column1,idxColumn6

key: column5_index,idxColumn6

key_len: 8,9

ref: NULL

rows: 7

filtered: 100.00

Extra: Using intersect(column5_index,idxColumn6); Using where

可以看到 EXTRA 列：

Using intersect(column5_index,idxColumn6)

从5.1开始，引入了 index merge 优化技术，对同一个表可以使用多个索引分别进行条件扫描。

相关文档：http://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.

【模拟与验证】

根据以上初步分析，猜测应该就是intersect造成的，于是在测试环境模拟验证，开启2个session模拟死锁：

时间序列	Session1	Session2
1	Begin；
2	UPDATE TestTable SET Column2 = sysdate() Column4 = 0 AND Column5 = 47 AND Column6 = 'SEK 执行成功，影响7行
3		Begin；
4		UPDATE TestTable SET Column2 = sysdate(),Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'；被Blocking
5	UPDATE TestTable SET Column2 = sysdate(),Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'；执行成功	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

依据以上信息可以发现Session2虽然被Block了，但也获取了一些Session1在时间序列5时所需资源的X锁，可以再开启一个查询select count(Column5) from TestTable where Column5 = 485，设置SET TRANSACTION ISOLATION LEVEL SERIALIZABLE，去查询Column5 = 485的行，观察锁等待的信息：
mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id

mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id \G;

*************************** 1. row ***************************

waiting_trx_id: 103006

waiting_thread: 36

waiting_query: UPDATE TestTable SET Column1 = 1, Column2 = sysdate(), Column3 = '026' Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'

blocking_trx_id: 103003

blocking_thread: 37

blocking_query: NULL

*************************** 2. row ***************************

waiting_trx_id: 421500433538672

waiting_thread: 39

waiting_query: select count(Column5) from TestTable where Column5 = 485

blocking_trx_id: 103006

blocking_thread: 36

blocking_query: UPDATE TestTable SET Column1 = 1, Column2 = sysdate(), Column3 = '026' Column4 = 0 AND Column5 = 485 AND Column6 = 'SEK'

2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits \G;

*************************** 1. row ***************************

requesting_trx_id: 103006

requested_lock_id: 103006:417:1493:859

blocking_trx_id: 103003

blocking_lock_id: 103003:417:1493:859

*************************** 2. row ***************************

requesting_trx_id: 421500433538672

requested_lock_id: 421500433538672:417:749:2

blocking_trx_id: 103006

blocking_lock_id: 103006:417:749:2

2 rows in set, 1 warning (0.00 sec)

mysql> select * from INNODB_LOCKS \G;

*************************** 1. row ***************************

lock_id: 103006:417:1493:859

lock_trx_id: 103006

lock_mode: X

lock_type: RECORD

lock_table: test.TestTable

lock_index: idxColumn6

lock_space: 417

lock_page: 1493

lock_rec: 859

lock_data: 'SEK', 8262738

*************************** 2. row ***************************

lock_id: 103003:417:1493:859

lock_trx_id: 103003

lock_mode: X

lock_type: RECORD

lock_table:test.TestTable

lock_index: idxColumn6

lock_space: 417

lock_page: 1493

lock_rec: 859

lock_data: 'SEK', 8262738

*************************** 3. row ***************************

lock_id: 421500433538672:417:749:2

lock_trx_id: 421500433538672

lock_mode: S

lock_type: RECORD

lock_table: test.TestTable

lock_index: column5_index

lock_space: 417

lock_page: 749

lock_rec: 2

lock_data: 485, 8317620

*************************** 4. row ***************************

lock_id: 103006:417:749:2

lock_trx_id: 103006

lock_mode: X

lock_type: RECORD

lock_table: test.TestTable

lock_index: column5_index

lock_space: 417

lock_page: 749

lock_rec: 2

lock_data: 485, 8317620

4 rows in set, 1 warning (0.00 sec)

可以看到Session2，trx_id 103006阻塞了trx_id 421500433538672，而trx_id 421500433538672 requested_lock也正好是lock_data: 485, 8317620。由此可见Session2虽然别block了，但是还是获取到了Index column5_index相关的锁。被Block是因为intersect的原因，还需要idxColumn6的锁，至此思路已经清晰，对整个分配锁的信息简化一下，如下表格（请求到的锁用青色表示，需获取但未获取到的锁用红色表示）：

时间点	Session1	Session2
1	477 SEK
2		485 SEK
3	485 SEK	死锁发生

可以看到485 SEK这两个资源形成了一个环状，最终发生死锁。

【解决方法】

最佳的方法是添加column5和Column6的联合索引。
我们环境当时的情况发现Column6的筛选度非常低，就删除了Column6的索引。
10:55左右删除索引后，报错没有再发生：

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。

您可能感兴趣的文章:

mysql报错：Deadlock found when trying to get lock; try restarting transaction的解决方法
线上MYSQL同步报错故障处理方法总结(必看篇)
MYSQL主从库不同步故障一例解决方法
一次MySQL慢查询导致的故障
MySQL下高可用故障转移方案MHA的超级部署教程
MySQL复制的概述、安装、故障、技巧、工具(火丁分享)
检测MySQL的表的故障的方法

内容总结

以上是互联网集市为您收集整理的MySQLDeadLock故障排查全过程记录全部内容，希望文章能够帮你解决MySQLDeadLock故障排查全过程记录所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/581845.html

来源：【匿名】

【上一篇】GDB调试Mysql实战之源码编译安装【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQLDeadLock故障排查全过程记录】教程文章相关的互联网学习教程文章

mysqldump 导出CSV文件

mysqldump -h 127.0.0.1 -u root -p database table -t -T /home/mysql/ --fields-enclosed-by=\" --fields-terminated-by=,-w 增加条件判断语句老外的一个处理mysql异常的错误，明天整理一下 http://icesquare.com/wordpress/mysql-starting-mysql-error-the-server-quit-without-updating-pid-file/原文：http://www.cnblogs.com/minbbp/p/3883805.html

使用mysqldump对数据库作备份

经理提出要求：对线上测试环境的mysql的所有数据库作备份[root@BJVM-CM-94 data]# cd /app/mysql/data/[root@BJVM-CM-94 data]# du -sh2.9G.经查看数据只有2.9G，数据容量不是很大，所以我选择了使用mysqldump进行备份[root@www ftp.scj.com]# ps -ef | grep mysqlroot 5642 1211 20 11:16 pts/0 00:00:00 grep mysqlroot 29558 1 0 Apr14 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadi...

CentOS 下安装python 之MySQLdb【代码】【图】

yum -y install mysql-devwget http://downloads.sourceforge.net/project/mysql-python/mysql-python-test/1.2.4b4/MySQL-python-1.2.4b4.tar.gz?r=http%3A%2F%2Fsourceforge.net%2Fprojects%2Fmysql-python%2F&ts=1364895531&use_mirror=nchctar zxvf MySQL-python-1.2.4b4.tar.gz cd MySQL-python-1.2.4b4 python setup.py build python setup.py installurllib2.URLError: <urlopen error [Errno 110] Connection timed out>...

Python安装MySQLdb【代码】

环境（1）：Centos 6.7 Python 2.6.x环境（2）：Ubuntu Python 2.7.x开始安装：环境（1）： sudo yum install mysqldb-python 环境（2）： sudo apt-get install python-mysqldb本文出自 “启思·朝圣者” 博客，请务必保留此出处http://dearch.blog.51cto.com/10423918/1789296原文：http://dearch.blog.51cto.com/10423918/1789296

mysqldump导出数据时，某些表不导出，排除某些表，不导出某些表【代码】

需求说明：　　今天一同事问，在通过mysqldump导出数据库的时候，能不能把某些表不导出，或者叫做排除在外呢，　　记得应该是可以实现，就搜索了下，通过mysqldump的--ignore-table参数能够实现，再次记录下。操作过程：1.查看某个库及其中的表的情况mysql> use mytest Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -ADatabase changed mysq...

[转]Mysql导入导出工具Mysqldump和Source命令用法详解【代码】

Mysql本身提供了命令行导出工具Mysqldump和Mysql Source导入命令进行SQL数据导入导出工作，通过Mysql命令行导出工具Mysqldump命令能够将Mysql数据导出为文本格式(txt)的SQL文件，通过Mysql Source命令能够将SQL文件导入Mysql数据库中，下面通过Mysql导入导出SQL实例详解Mysqldump和Source命令的用法。在PHP网站开发中，时常遇到Mysql数据库备份或数据库迁移工作，这时Mysql怎么导入导出数据库中的数据就非常关键，Mysql本身提供了命...

mysqldiff对比主从表结构是否一致【代码】

mysqldiff该工具是官方mysql-utilities工具集的一个脚本，可以实现主从服务器表结构是否一致。数据校验需要使用Percona的pt-table-checksum工具。安装：# tar zxvf mysql-utilities-1.5.4.tar.gz # cd mysql-utilities-1.5.4 # python setup.py install使用方法：mysqldiff --server1=admin:123456@192.168.79.10 --server2=admin:123456@192.168.79.11 --difftype=differ test:test # WARNING: Using a password on the command ...

MySQLdb库连接MySQL数据库

Cacti是一套基于PHP,MySQL,SNMP及RRDTool开发的网络流量监测图形分析工具。Cacti是通过 snmpget来获取数据，使用 RRDtool绘画图形，而且你完全可以不需要了解RRDtool复杂的参数。它提供了非常强大的数据和用户管理功能，可以指定每一个用户能查看树状结构、host以及任何一张图，还可以与LDAP结合进行用户验证，同时也能自己增加模板，功能非常强大完善。界面友好。软件 Cacti 的发展是基于让 RRDTool 使用者更方便使用该软件，除了...

MySQL逻辑备份mysqldump【代码】

一个用mysqldump做逻辑备份的小脚本用于小数据量的备份，全库，按日期，按库，按表，进行分类# date -s "2015/01/01" && bash mybackup.sh#！/bin/bash# 使用mysqldump进行逻辑备份 # 目录结构/backup/日期 # 每隔7天删除旧的备份目录db_backup_root="/backup" today=`date +%Y%m%d`old_backup_dir=`date -d "-7 day" +%Y%m%d`db_user="root" db_passwd="geekoo" sock=`mysql -u$db_user -p$db_passwd -e "show variables like ‘...

Python 的 MySQLdb 模块

import MySQLdbtry: conn = MySQLdb.connect(host=‘‘,user=‘‘,passwd=‘‘,db=‘‘,port=) cur = conn.cursor() conn.select_db(‘db_name‘) value = [1,‘colben‘] cur.execute(‘insert into test values(%s,%s)‘,value) values = [(1,‘colben‘),(2,‘shelly‘)] cur.executemany(‘insert into test values(%s,%s)‘,values) cur.fetchall() cur.fetchmany(size) cur.fetchone() con...

Python3.4下安装pip和MySQLdb

想用pyhton3.4做数据分析，pip和MySQLdb是必要的，一个便于安装常用模块，一个用来操作数据库。当时安装这两个模块时，由于没有人指导，花了很多的时间才安装好。安装pip时，按照网上的教程，先在链接https://pypi.python.org/pypi/pip里下载了pip8.1，按照常规方法，解压后执行python setup.py install。发现报错了“ImportError：No module named ‘pip’ ”，也不知道是什么情况，到处找资料，还是没哟解决了。最后只好试试把安...

PYTHON -MYSQLDB安装遇到的问题和解决办法

目前下载的mysqldb在window下没有exe安装包了，只有源码。使用python setup.py install 命令安装，报错如下：异常信息如下：F:\devtools\MySQL-python-1.2.3>pythonsetup.py buildTraceback (most recent call last): File "setup.py", line 15, in <module> metadata, options = get_config() File "F:\devtools\MySQL-python-1.2.3\setup_windows.py", line7, in get_config serverKey = _winreg.OpenKey(_winreg.HKEY_LOCAL...

MySQLdb模块操作【代码】

Linux安装mysql： apt-get install mysql-server安装python-mysql模块：apt-get install python-mysqldbWindows下载安装mysqlpython操作mysql模块：MySQL-python-1.2.3.win32-py2.7.exe 或 MySQL-python-1.2.3.win-amd64-py2.7.exemysql图形界面：Navicat_for_MySQL安装完成后，导入MySQLdb测试是否安装成功#!/usr/bin/env python #coding:utf-8import MySQLdb‘‘‘ conn = MySQLdb.connect(host=‘127.0.0.1‘,user=‘root‘,pas...

python2.7安装MySQLdb【代码】

安装MySQLdb遇到的问题。mac版本号：10.11.6执行 python2.7 setup.py build报错：urllib2.URLError: <urlopen error [SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed (_ssl.c:590)>错误代码是590网上查到的资料都是说mac电脑同时存在很多个版本的python造成的，有的也说是os系统造成的。验证阶段：1.直接使用python setup.py build，没有问题，直接运行成功了。排除os系统问题2.在网上搜到的原因是ssl验证的问题，那么...

解决linux（centos7）重新安装mysql systemctl start mysqld.service时报错

重新安装mysql时，运行systemctl status mysqld.service报错。[root@test-dcruxue ~]# systemctl start mysqld.serviceJob for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.查看mysql服务状态[root@test-dcruxue ~]# systemctl status mysqld.service● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/syst...

首页 / MYSQL / MySQLDeadLock故障排查全过程记录

MySQLDeadLock故障排查全过程记录

内容导读

内容图文

您可能感兴趣的文章:

内容总结

内容备注

内容手机端

【MySQLDeadLock故障排查全过程记录】教程文章相关的互联网学习教程文章

MYSQLD - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程