search
HomeDatabaseMysql TutorialMySQL Innodb表导致死锁日志情况分析与归纳

发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志

案例描述
在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。
两个sql语句如下:
(1)insert into backup_table select * from source_table
(2)DELETE FROM source_table WHERE Id>5 AND titleWeightteamUser表的表结构如下:
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),
ENGINE=InnoDB
两语句对source_table表的使用情况如下:

死锁日志打印出的时间点表明,语句(1)运行过程中,当语句(2)开始运行时,发生了死锁。
当mysql检测出死锁时,除了查看mysql的日志,还可以通过show InnoDB STATUS \G语句在mysql客户端中查看最近一次的死锁记录。由于打印出来的语句会很乱,所以,最好先使用pager less命令,通过文件内容浏览方式查看结果,会更清晰。(以nopager结束)
得到的死锁记录如下:


根据死锁记录的结果,可以看出确实是这两个语句发生了死锁,且锁冲突发生在主键索引上。那么,为什么两个sql语句会存在锁冲突呢?冲突为什么会在主键索引上呢?语句(2)得到了主键索引锁,为什么还会再次申请锁呢?
锁冲突分析
2.1 innodb的事务与行锁机制
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关,MyISAM不支持事务、采用的是表级锁,而InnoDB支持ACID事务、 行级锁、并发。MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句,从而有效的将每条语句作为一个单独的事务来处理。
2.2 两语句加锁情况
在innodb默认的事务隔离级别下,普通的SELECT是不需要加行锁的,但LOCK IN SHARE MODE、FOR UPDATE及高串行化级别中的SELECT都要加锁。有一个例外,此案例中,语句(1)insert into teamUser_20110121 select * from teamUser会对表teamUser_20110121(ENGINE= MyISAM)加表锁,并对teamUser表所有行的主键索引(即聚簇索引)加共享锁。默认对其使用主键索引。
而语句(2)DELETE FROM teamUser WHERE teamId=$teamId AND titleWeight2.3 锁冲突的产生
由于共享锁与排他锁是互斥的,当一方拥有了某行记录的排他锁后,另一方就不能其拥有共享锁,同样,一方拥有了其共享锁后,另一方也无法得到其排他锁。所 以,当语句(1)、(2)同时运行时,相当于两个事务会同时申请某相同记录行的锁资源,于是会产生锁冲突。由于两个事务都会申请主键索引,锁冲突只会发生 在主键索引上。
常常看到一句话:在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的。那就说明,单个SQL组成的事务锁是一次获得的。而此案例中,语句(2) 已经得到了主键索引的排他锁,为什么还会申请主键索引的排他锁呢?同理,语句(1)已经获得了主键索引的共享锁,为什么还会申请主键索引的共享锁呢?
死锁记录中,事务一等待锁的page no与事务二持有锁的page no相同,均为218436,这又代表什么呢?
我们的猜想是,innodb存储引擎中获得行锁是逐行获得的,并不是一次获得的。下面来证明。
死锁产生过程分析
要想知道innodb加锁的过程,唯一的方式就是运行mysql的debug版本,从gdb的输出中找到结果。根据gdb的结果得到,单个SQL组成的事 务,从宏观上来看,锁是在这个语句上一次获得的,但从底层实现上来看,是逐个记录行查询,得到符合条件的记录即对该行记录的索引加锁。
Gdb结果演示如下:
代码如下:
(gdb) b lock_rec_lock
 Breakpoint 1 at 0×867120: file lock/lock0lock.c, line 2070.
 (gdb) c
 Continuing.
 [Switching to Thread 1168550240 (LWP 5540)]
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01c1 “789\200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 Current language: auto; currently c
 (gdb) c
 Continuing.
 Breakpoint 1, lock_rec_lock (impl=0, mode=1029, rec=0x2aedbc80ba “\200″, index=0x2aada730b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 (gdb) c
 Continuing.
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01cf “789\200″, index=0x2aada734b8, thr=0x2aada74c18) at lock/lock0lock.c:2070
 2070 {
 (gdb) c
 Continuing.


(说明:”789\200″为非聚簇索引,”\200″为主键索引)

Gdb结果显示,语句(1)(2)加锁的获取记录为多行,即逐行获得锁,这样就解释了语句(2)获得了主键索引锁还再次申请主键索引锁的情况。
由于语句(1)使用了主键索引,而语句(2)使用了非聚簇索引,两个事务获得记录行的顺序不同,而加锁的过程是边查边加、逐行获得,于是,就会出现如下情况:

于是,两个事务分别拥有部分锁并等待被对方持有的锁,出现这种资源循环等待的情况,即死锁。此案例中被检测时候的锁冲突就发现在page no为218436和218103的锁上。
InnoDB 会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。Innodb会选择代价比较小的事务回滚,此次事务(1)解锁并回滚,语句(2)继续运行直至事务结束。
innodb死锁形式归纳
死锁产生的四要素:互斥条件:一个资源每次只能被一个进程使用;请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放;不剥夺条件:进程 已获得的资源,在末使用完之前,不能强行剥夺;循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
Innodb检测死锁有两种情况,一种是满足循环等待条件,还有另一种策略:锁结构超过mysql配置中设置的最大数量或锁的遍历深度超过设置的最大深度 时,innodb也会判断为死锁(这是提高性能方面的考虑,避免事务一次占用太多的资源)。这里,我们只考虑满足死锁四要素的情况。
死锁的形式是多样的,但分析到innodb加锁情况的最底层,因循环等待条件而产生的死锁只有可能是四种形式:两张表两行记录交叉申请互斥锁、同一张表则存在主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级导致的锁等待队列阻塞。
以下首先介绍innodb聚簇索引与非聚簇索引的数据存储形式,再以事例的方式解释这四种死锁情况。
4.1聚簇索引与非聚簇索引介绍
聚簇索引即主键索引,是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序,聚簇索引的索引页面指针指向数据页面。非聚簇索引(即第二主键索 引)不重新组织表中的数据,索引顺序与数据物理排列顺序无关。索引通常是通过B-Tree数据结构来描述,那么,聚簇索引的叶节点就是数据节点,而非聚簇 索引的叶节点仍然是索引节点,通常是一个指针指向对应的数据块。
而innodb在非聚簇索引叶子节点包含了主键值作为指针。(这样是为了减少在移动行或数据分页时索引的维护工作。)其结构图如下:

当使用非聚簇索引时,会根据得到的主键值遍历聚簇索引,得到相应的记录。
4.2四种死锁情况
在InnoDB中,使用行锁机制,于是,锁通常是逐步获得的,这就决定了在InnoDB中发生死锁是可能的。
即将分享的四种死锁的锁冲突分别是:不同表的相同记录行索引锁冲突、主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级造成锁队列阻塞。
不同表的相同记录行锁冲突
案例:两个表、两行记录,交叉获得和申请互斥锁

条件:
A、 两事务分别操作两个表、相同表的同一行记录
B、 申请的锁互斥
C、 申请的顺序不一致

主键索引锁冲突
案例:本文案例,产生冲突在主键索引锁上
条件:
A、 两sql语句即两事务操作同一个表、使用不同索引
B、 申请的锁互斥
C、 操作多行记录
D、 查找到记录的顺序不一致

主键索引锁与非聚簇索引锁冲突
案例:同一行记录,两事务使用不同的索引进行更新操作

此案例涉及TSK_TASK表,该表相关字段及索引如下:
ID:主键;
MON_TIME:监测时间;
STATUS_ID:任务状态;
索引:KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

条件:
A、 两事务使用不同索引
B、 申请的锁互斥
C、 操作同一行记录

当执行update、delete操作时,会修改表中的数据信息。由于innodb存储引擎中索引的数据存储结构,会根据修改语句使用的索引以及修改信息 的不同执行不同的加锁顺序。当使用索引进行查找并修改记录时,会首先加使用的索引锁,然后,如果修改了主键信息,会加主键索引锁和所有非聚簇索引锁,修改 了非聚簇索引列值会加该种非聚簇索引锁。
此案例中,事务一使用非聚簇索引查找并修改主键值,事务二使用主键索引查找并修改主键值,加锁顺序不同,导致同时运行时产生资源循环等待。
锁升级造成锁队列阻塞
案例:同一行记录,事务内进行锁升级,与另一等待锁发送锁队列阻塞,导致死锁

条件:
A、 两事务操作同一行记录
B、 一事务对某一记录先申请共享锁,再升级为排他锁
C、 另一事务在过程中申请这一记录的排他锁

避免死锁的方法
InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供非锁定读。这些特色增加了多用户部署和性能。
但其行锁的机制也带来了产生死锁的风险,这就需要在应用程序设计时避免死锁的发生。以单个SQL语句组成的隐式事务来说,建议的避免死锁的方法如下:
1.如果使用insert…select语句备份表格且数据量较大,在单独的时间点操作,避免与其他sql语句争夺资源,或使用select into outfile加上load data infile代替 insert…select,这样不仅快,而且不会要求锁定
2. 一个锁定记录集的事务,其操作结果集应尽量简短,以免一次占用太多资源,与其他事务处理的记录冲突。
3.更新或者删除表格数据,sql语句的where条件都是主键或都是索引,避免两种情况交叉,造成死锁。对于where子句较复杂的情况,将其单独通过sql得到后,再在更新语句中使用。
4. sql语句的嵌套表格不要太多,能拆分就拆分,避免占有资源同时等待资源,导致与其他事务冲突。
5. 对定点运行脚本的情况,避免在同一时间点运行多个对同一表进行读写的脚本,特别注意加锁且操作数据量比较大的语句。
6.应用程序中增加对死锁的判断,如果事务意外结束,重新运行该事务,减少对功能的影响。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
golang函数并发控制中死锁与饥饿的预防与解决golang函数并发控制中死锁与饥饿的预防与解决Apr 24, 2024 pm 01:42 PM

Go中死锁和饥饿:预防与解决死锁:协程相互等待而无法进行的操作,使用runtime.SetBlockProfileRate函数检测。预防死锁:使用细粒度加锁、超时、无锁数据结构,防止死锁。饥饿:协程持续无法获得资源,使用公平锁防止饥饿。公平锁实践:创建公平锁并等待协程尝试获取锁的时间最长的优先获取锁。

如何处理C++开发中的死锁问题如何处理C++开发中的死锁问题Aug 22, 2023 pm 02:24 PM

如何处理C++开发中的死锁问题死锁是多线程编程中常见的问题之一,尤其是在使用C++进行开发时更容易遇到。当多个线程互相等待对方持有的资源时,就可能发生死锁问题。如果不及时处理,死锁不仅会导致程序卡死,还会影响系统的性能和稳定性。因此,学习如何处理C++开发中的死锁问题是非常重要的。一、理解死锁的原因要解决死锁问题,首先需要了解死锁产生的原因。死锁通常发生在以

Go开发中解决死锁的方法Go开发中解决死锁的方法Jun 30, 2023 pm 04:58 PM

解决Go语言开发中的死锁问题的方法Go语言是一种开源的静态类型编译型语言,被广泛应用于并发编程。然而,由于Go语言的并发模型的特性,开发者在编写并发程序时常常会遇到死锁问题。本文将介绍一些解决Go语言开发中死锁问题的方法。首先,我们需要了解何为死锁。死锁是指多个并发任务因互相等待对方释放资源而无法继续执行的情况。在Go语言中,死锁问题通常由于对资源的竞争或者

C++ 多线程编程中死锁预防和检测机制C++ 多线程编程中死锁预防和检测机制Jun 01, 2024 pm 08:32 PM

多线程死锁预防机制包括:1.锁顺序;2.测试并设置。检测机制包括:1.超时;2.死锁检测器。文章举例共享银行账户,通过锁顺序避免死锁,为转账函数先请求转出账户再请求转入账户的锁。

如何调试 C++ 程序中的死锁?如何调试 C++ 程序中的死锁?Jun 03, 2024 pm 05:24 PM

死锁是一种并发编程中的常见错误,发生在多个线程等待彼此持有的锁时。可以通过使用调试器检测死锁,分析线程活动并识别涉及的线程和锁,从而解决死锁。解决死锁的方法包括避免循环依赖、使用死锁检测器和使用超时。在实践中,通过确保线程按相同的顺序获取锁或使用递归锁或条件变量可以避免死锁。

系统出现死锁有哪些原因系统出现死锁有哪些原因Sep 04, 2023 pm 02:44 PM

系统出现死锁的原因有互斥条件、请求与保持条件、不可剥夺条件和循环等待条件。详细介绍:1、互斥条件,多个线程需要同时访问某些共享资源,而这些资源一次只能被一个线程占用,如果一个线程占用了某个资源,其他线程就必须等待该资源释放;2、请求与保持条件,一个线程在持有某个资源的同时,又请求获取其他线程持有的资源,如果这些资源被其他线程占用,就会导致线程等待;3、不可剥夺条件等等。

Python 中的并发编程难题:与死锁和竞态条件作战Python 中的并发编程难题:与死锁和竞态条件作战Feb 19, 2024 pm 02:40 PM

死锁死锁是指多个线程相互等待资源,从而形成一个循环,最终导致所有线程都阻塞。在python中,死锁通常发生在对多个锁或互斥量按错误顺序进行锁定时。示例:importthreading#两个线程共享两个锁lock1=threading.Lock()lock2=threading.Lock()defthread1_func():lock1.acquire()lock2.acquire()#做一些操作lock2.release()lock1.release()defthread2_func():loc

如何解决Go语言中的死锁问题?如何解决Go语言中的死锁问题?Oct 08, 2023 pm 05:07 PM

如何解决Go语言中的死锁问题?Go语言具有并发编程的特性,可以通过使用goroutine和channel来实现并发操作。然而,在并发编程中,死锁是一个常见的问题。当goroutine之间相互依赖于彼此的资源,并且在访问这些资源时产生了循环依赖关系,就可能导致死锁的发生。本文将介绍如何解决Go语言中的死锁问题,并提供具体的代码示例。首先,让我们来了解一下什么是

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment