线上遇到了MySQL死锁的相关问题,需要查看MySQL出现的Deadlock日志,可以通过执行:
show engine innodb status
来查看innodb类型数据库的状态,查找laster detected deadlock部分,可以看到最近造成死锁的两条sql
------------------------ LATEST DETECTED DEADLOCK ------------------------ 161020 17:58:11 *** (1) TRANSACTION: TRANSACTION ED354BF4, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 2938474, OS thread handle 0x2b9ffd19b940, query id 3121991643 192.168.1.163 apitest140715 Updating UPDATE xxx SET fix_stock=fix_stock+-1 WHERE aaa = 1 AND aaa=101488 AND fix_stock+-1>=0 AND stock>=fix_stock+-1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 196984 page no 743 n bits 1272 index `xxxx` of table `xxx`.`xxxx` trx id ED354BF4 lock_mode X waiting Record lock, heap no 581 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80018c70; asc p;; 1: len 4; hex 80018ce8; asc ;; *** (2) TRANSACTION: TRANSACTION ED354C8C, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 6 lock struct(s), heap size 1248, 4 row lock(s) MySQL thread id 2938340, OS thread handle 0x2b9ffcae8940, query id 3121991660 192.168.1.115 163test Updating update xxx set fix_stock=fix_stock+1 where product_spec_id=101488 and fix_stock+1>=0 and stock>=fix_stock+1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 196984 page no 743 n bits 1272 index `xxx` of table `shop_zp`.`gt_goods_warehouse_index` trx id ED354C8C lock_mode X Record lock, heap no 581 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80018c70; asc p;; 1: len 4; hex 80018ce8; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 196974 page no 2114 n bits 176 index `PRIMARY` of table `xxxx`.`xxxx` trx id ED354C8C lock_mode X locks rec but not gap waiting Record lock, heap no 85 PHYSICAL RECORD: n_fields 35; compact format; info bits 0 0: len 4; hex 00018c70; asc p;; 1: len 6; hex 0000ed354bf4; asc 5K ;; 2: len 7; hex 7600011487203d; asc v =;; 3: len 1; hex 00; asc ;; 4: len 4; hex 80000000; asc ;; 5: len 1; hex 00; asc ;; 6: len 0; hex ; asc ;; 7: len 12; hex 373030323335343037303836; asc 700235407086;; 8: len 0; hex ; asc ;; 9: len 4; hex 0010225f; asc "_;; 10: len 4; hex 00000338; asc 8;; 11: len 4; hex 800186a0; asc ;; 12: len 4; hex 80000056; asc V;; 13: len 4; hex 80000000; asc ;; 14: len 4; hex 80000000; asc ;; 15: len 9; hex 800000000000000577; asc w;; 16: len 5; hex 8000000000; asc ;; 17: len 5; hex 8000000000; asc ;; 18: len 1; hex 81; asc ;; 19: len 9; hex 800000000000000af0; asc ;; 20: len 1; hex 80; asc ;; 21: len 4; hex 0000011b; asc ;; 22: len 4; hex 000000e0; asc ;; 23: len 4; hex 80000000; asc ;; 24: len 4; hex 80000000; asc ;; 25: len 1; hex 81; asc ;; 26: len 4; hex d5684647; asc hFG;; 27: len 4; hex 58089533; asc X 3;; 28: len 0; hex ; asc ;; 29: len 5; hex 800002e505; asc ;; 30: len 5; hex 8000036303; asc c ;; 31: len 4; hex 000f4240; asc B@;; 32: len 4; hex 80000000; asc ;; 33: len 4; hex 0000803f; asc ?;; 34: SQL NULL; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------
在编写程序的时候,在各个事务内部,对表的修改顺序最好一致(比如对所有表进行编号,尽量先修改编号小或者大的表),这样可以避免大多数的死锁。StackOverflow中就有这种如何避免mysql死锁的相关方法:http://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans,
MySQL锁机制
相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
MySQL这3种锁的相关指标可以参考:开销、加锁速度、死锁、粒度、并发性能:
- 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
- 行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
- 页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。这一点在本书的“开发篇”介绍表类型的选择时,也曾提到过。下面几节我们重点介绍MySQL表锁和 InnoDB行锁的问题,由于BDB已经被InnoDB取代,即将成为历史,在此就不做进一步的讨论了。
我们当前使用的是InnoDB,与MyISAM的最大不同有两点:一是支持事务,二是采用了行级锁,行级锁与表级锁有很多不同之处。
引入数据库的事务支持之后,相对于串行处理来说,并发事务处理能够大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,支持更多用户,但同时并发用户也同时带来一些问题:
- 更新丢失(Lost Update):当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题--最后的更新覆盖了由其他事务所做的更新。例如,两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题。
- 脏读(Dirty Reads):一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
- 不可重复读(Non-Repeatable Reads):一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”。
- 幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。
防止上述问题有些并不是通过数据库事务控制器来解决,例如“更新丢失”,需要应用程序对要更新的数据加上必要的锁来解决,这些应该是应用的责任,其他三种其实都是数据库一致性问题,必须由数据库提供一定的事务隔离机制,数据库实现事务隔离的方式基本上可以分成以下两种:
- 读取数据前,对其加锁,阻止其他事务对数据进行修改;
- 不用加任何锁,通过一定机制生成一个数据请求时间点的一致性数据快照,并用这个快照来提供一定级别,也成为多版本并发控制。
数据库的事务隔离越是严格,并发副作用就越小,付出的代价就越大,因为事务隔离实质上就是使得事务在一定程度上”串行化“进行,与并发相矛盾的。
为了解决隔离与并发之间的矛盾,ISO/ANSI SQL92定义了4个事务隔离级别,每个级别的隔离程度不同,允许出现的副作用也就不同,应用可以根据自己的业务逻辑要求选择不同的隔离级别来平衡隔离与并发之间的矛盾。
读数据一致性 | 脏读 | 不可重复读 | 幻读 | |
未提交读 | 最低级别 | 是 | 是 | 是 |
已提交读 | 语句级 | 否 | 是 | 是 |
可重复读 | 事务级 | 否 | 否 | 是 |
可序列化 | 最高级别,事务级 | 否 | 否 | 否 |
注意:各个具体数据库并不一定完全实现上述4个隔离级别。
InnoDB的行锁模式以及加锁方法
共享锁:允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁;
排他锁:允许获得排它锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁;
意向共享锁:事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的意向共享锁;
意向排他锁:事务打算给数据行加行排他锁,事务在给一个数据行加排它锁前必须先取得该表的意向排他锁。
意向锁是InnoDB自动加的,不需要用户干预,对于update, insert, delete语句InnoDB会自动给涉及数据集加排他锁,对于普通select语句InnoDB不会加任何锁,事务可以通过以下语句显式地给记录集加上共享锁和排他锁。
select * from table_name where … lock in share mode; //共享锁 select * from table_name where … for update; //排它锁
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点与Oracle不同,后者是通过在数据块中对相应数据行加锁实现的,InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则将使用表锁!
由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,虽然访问不同行的记录,但如果使用相同的索引键是会出现锁冲突的。
当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,不论是使用主键索引、唯一键索引或是普通索引,InnoDB都会使用行锁来对数据加锁。
但有一种特殊情况,即便在条件中使用了索引字段,是否使用索引来检索数据是由MySQL通过判断不同的执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,这种情况将会使用表锁而不是行锁。因此在分析锁冲突时,需要检查SQL执行计划以确认是否真正使用了索引。
对于InnoDB表,在绝大多数情况下都应该使用行级锁,因为事务和行锁往往是我们之所以选择InnoDB表的理由,但个别特殊事务中,也可以考虑使用表级锁:
- 当事务需要更新大部分或全部数据,表又比较大,如果使用默认的行锁,不仅该事务执行效率低,而且可能造成其他事务长时间锁等待和锁冲突;
- 事务涉及到多个表,比较复杂,很可能引起死锁,造成大量事务回滚,这种情况也可以考虑一次性锁定事务涉及表,从而避免死锁,减少数据库因事务回滚带来的开销。
MyISAM表锁是deadlock free的,因为MyISAM总是一次获得所需的全部锁,要么全部满足要么等待,因此不会出现死锁,但在InnoDB中,除了单个SQL组成事务外,锁是逐步获得的,这就决定了在InnoDB中发生死锁是有可能的。
发生死锁后,InnoDB一般都能够自动检测到,并使一个事务释放锁并回退,另一个事务获得锁继续完成事务。但在涉及外部锁或涉及表锁的情况下,InnoDB并不能完全检测到死锁,这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获得所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖跨数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。
通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小,以及访问数据库的SQL语句,绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法。
相关推荐
在 MySQL 中,死锁检测机制是一种重要的机制,用于检测和解决事务之间的死锁问题。在本文中,我们将详细介绍 MySQL 死锁检测机制的原理和实现。 一、死锁的定义和原理 在 MySQL 中,死锁是指两个或两个以上的事务...
标题和描述中提到的事件是一次在线MySQL数据库发生的死锁问题,这突显了了解数据库加锁原理的重要性,而不仅仅是基础的CRUD操作。死锁是指两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无...
MySQL数据库在处理并发事务时,可能会遇到一种特殊的情况,即死锁。死锁是指两个或多个事务在执行过程中,因争夺资源而造成的一种相互等待的现象,若无外力干涉它们将无法继续执行。死锁是数据库系统中常见的问题,...
本文将详细介绍MySQL中的死锁现象、其产生的原因以及如何有效地检测与解决死锁问题。 #### 一、死锁定义 死锁是指两个或两个以上的事务在执行过程中,由于每个事务都持有某些资源且又都在等待其他事务释放其持有的...
### MySQL死锁分析 #### 死锁问题背景 在MySQL的使用过程中,死锁是一个较为常见的现象,尤其是在并发量较大的应用场景下。死锁的发生往往会给系统带来不可预知的影响,严重时甚至会导致整个数据库服务不可用。...
MySQL中的死锁问题是一个复杂而微妙的议题,尤其是在数据库优化和并发控制中。本文将深入探讨一个看似不可能发生的死锁...在遇到类似死锁问题时,应从多个角度出发,逐一排查可能的原因,确保数据库系统的高效运行。
在工作过程中偶尔会遇到死锁问题,虽然这种问题遇到的概率不大,但每次遇到的时候要想彻底弄懂其原理并找到解决方案却并不容易。这个项目收集了一些常见的 MySQL 死锁案例,大多数案例都来源于网络,并对其进行分类...
MySQL 死锁案例详解 在 MySQL 中,死锁是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象。死锁的产生原因是由于两个或两个以上的 Session 加锁的顺序不一致。解决死锁问题的关键就是让...
在工作和学习的过程中偶尔会遇到死锁问题,虽然这种问题遇到的概率不大,但每次遇到的时候要想彻底弄懂其原理并找到解决方案却并不容易。这个项目收集了一些常见的 MySQL 死锁案例,大多数案例都来源于网络,并对其...
MySQL 遇到过死锁问题吗,你是如何解决的?.md
MySQL数据库的死锁问题是一个复杂且常见的挑战,尤其是在高并发的业务环境中。死锁发生在两个或更多的事务相互等待对方释放资源,导致它们无法继续执行。这种情况严重影响系统的性能和用户体验,因此,理解底层源码...
本文将以一个具体的死锁案例为背景,深入分析MySQL中的死锁机制,探讨死锁的成因,并提出预防策略,旨在帮助读者清晰理解死锁问题的背景、阅读死锁日志的方法、深入剖析死锁的原因以及总结死锁问题。 首先,死锁...
在工作过程中偶尔会遇到死锁问题,虽然这种问题遇到的概率不大,但每次遇到的时候要想彻底弄懂其原理并找到解决方案却并不容易。这个项目收集了一些常见的 MySQL 死锁案例,大多数案例都来源于网络,并对其进行分类...
然而,随着并发操作的增加,数据库可能会遇到一种特殊的问题——死锁。死锁是指两个或多个事务在等待对方释放资源,从而导致它们都无法继续执行的情况。本文将深入探讨MySQL中的死锁现象,以及如何识别、避免和解决...
35丨记一次线上SQL死锁事故:如何避免死锁?.html
前段时间遇到了一个Mysql 死锁相关的问题,整理一下。 问题描述:Mysql 的修改语句似乎都没有生效,同时使用Mysql GUI 工具编辑字段的值时会弹出异常。 什么是死锁 在解决Mysql 死锁的问题之前,还是先来了解一下...
MySQL的InnoDB存储引擎在处理并发事务时可能会遇到死锁问题,这主要发生在多个事务互相等待对方释放资源的情况下。死锁通常由四个必要条件引发: 1. 互斥条件:资源只能被一个事务使用。 2. 请求和保持条件:一个...