MySQL技术内幕 InnoDB存储引擎：阻塞、死锁、锁升级_innodv引擎导致语句查询被堵住

1、堵塞

因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另外一个事务中的锁释放它所占用的资源，这就是堵塞。

• 参数innodb_lock_wait_timeout用来控制等待的时间，默认50秒，是可以动态设置的。
• 参数innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作。默认是OFF。（静态参数，无法是mysql运行时修改）

在默认情况下InnoDB存储引擎不会回滚超时引发的错误异常。比如一个事务中执行两条insert 语句，第一条正常执行，第二条出现超时异常，这时innodb不会回滚整个事务，即第一条insert语句，已经提交到数据库。

2、死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待现象。

解决死锁问题最简单方式是不要有等待，将任何的等待都转化为回滚，并且事务重新开始。毫无疑问，这的确可以避免死锁问题的产生。但是性能很差。

解决死锁问题最简单方法是超时，即当两个事务互相等待时，当一个等待时间超过阈值时，其中一个事务进行回滚，另外一个事务就能继续进行。虽然简单，但是仅仅根据FIFO进行回滚，若超时的事务占据权重比较大，会浪费较多时间。

当前数据库都普遍采用wait-for graph(等待图)的方式来进行死锁检测。可以发现wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说，InnoDB存储引擎会选择回滚undo量最小的事务

InnoDB存储引擎并不会回滚大部分的错误异常，但是死锁除外。发现死锁后，InnoDB存储引擎会马上回滚一个事务，这点是需要注意的。如果在应用程序中捕获了1213这个错误，其实并不需要对其进行回滚。

3、锁升级

锁升级（Lock Escalation）是指将当前锁的粒度降低。举例来说，数据库可以把一个表的1 000个行锁升级为一个页锁，或者将页锁升级为表锁。如果数据库的设计中认为锁是一种稀有资源，而且想避免锁的开销，那数据库中会频繁出现锁升级现象。

Microsoft SQL Server数据库的设计认为锁是一种稀有的资源，在适合的时候会自动地将行、键或者分页级锁升级为更粗粒度的表级锁。这种升级保护了系统资源，防止系统使用太多的内存来维护锁，从一定程度上提高了效率。

即使在Microsoft SQL Server 2005的版本之后，SQL Server数据库支持了行锁，但是其设计和InnoDB存储引擎完全不同，在以下情况下依然可能发生锁升级：

• 由一句单独的SQL语句在一个对象上持有的锁数量超过了阈值，默认的这个阈值为5000。值得注意的是，如果是不同对象的话，则不会发生锁升级。
• 锁资源占用的内存超过了激活内存的40%时，就会发生锁升级。

在Microsoft SQL Server数据库中，由于锁是一种稀有的资源，因此锁升级会带来一定的效率提髙。但是锁升级带来的一个问题却是因为锁粒度的降低而导致并发性能的降低。

IrnioDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

假设一张表有3 000 000个数据页，每个页大约有100条记录，那么总共有300 000 000条记录。若有一个事务执行全表更新的SQL语句，则需要对所有记录加X锁。若根据每行记录产生锁对象进行加锁，并且每个锁占用10字节，则仅对锁管理就需要差不多需要3GB的内存。而InnoDB存储引擎根据页进行加锁，并采用位图方式，假设每个页存储的锁信息占用30个字节，则锁对象仅需90MB的内存。由此可见两者对于锁资源开销的差距之大。

本文整理自：《MySQL技术内幕 InnoDB存储引擎》
个人微信公众号：

作者：jiankunking 出处：http://blog.csdn.net/jiankunking