总结线上遇到的MySQL死锁问题

 

线上遇到了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
------------

 

 

 

在编写程序的时候，在各个事务内部，对表的修改顺序最好一致（比如对所有表进行编号，尽量先修改编号小或者大的表），这样可以避免大多数的死锁。*中就有这种如何避免mysql死锁的相关方法：http://*.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语句，绝大部分死锁都可以避免。下面就通过实例来介绍几种避免死锁的常用方法。