MySQL技术内幕Innodb存储引擎 六 锁

基本知识

• MyISAM引擎：表锁设计

• Microsoft SQL Server： 2005前是页锁，2005后开始支持乐观并发和悲观并发，在乐观并发下开始支持行级锁，但和InnoDB存储引擎的实现方式完全不同。此外，它会锁升级，行锁会升级到表锁

• Innodb：实现和Oracle数据库非常类似，提供一致性的非锁定读、行级锁支持。

• 可以通过 INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS 监控当前事务并分析可能存在的锁问题，见书p255

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

一致性非锁定读

• READ COMMITED：对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据
• REPEATABLE READ：对于快照数据，非一致性读总是读取事务开始时的行数据版本。

一致性锁定读

• InnoDB存储引擎对于Select语句支持两种一致性的锁定读

SELECT ... FOR UPDATE   上X锁
SELECT ... LOCK IN SHARE MODE  上S锁

外键和锁

• innodb存储引擎会自动对外键列加上索引（如果没有显式地对这个列加索引的话），因为这样可以避免表锁。
• 如果使用针对InnoDB的表使用行锁，被锁定字段不是主键，也没有针对它建立索引的话。行锁锁定的也是整张表

锁的算法

• Record Lock
• Gap Lock
• Next-Key Lock
• 当查询的索引含有唯一属性时(唯一索引只有一个列)，InnoDB存储引擎会对Next-Key Lock 进行优化，将其降低为Record Lock
• 在可重复读的隔离级别下，InnoDB存储引擎采取Next-Key Locking机制来避免幻读，在这个算法下，对于索引的扫描，不仅是锁住扫描到的索引，而且还锁住这些索引覆盖的范围
• 在READ COMMITED下，采用Record Lock

锁问题

• 脏读
• 不可重复读：可以通过next-key locking算法来避免‘
• 丢失更新：需要让事务在这种情况下的操作变成串行化，而不是并行操作。一般都是应用中存在一些逻辑意义的问题导致，而不是数据库理论意义上的丢失更新

死锁

概念

• 解决方法：

  • 超时回滚，设置innodb_lock_wait_timeout设置超时的时间，根据FIFO的顺序选择回滚对象，但不考虑超时事务所占的权重
  • wait-for-graph：（主动的死锁检测方式），若存在死锁，InnoDB选择回滚undo量小的事务
    • 锁的信息链表
    • 事务等待链表

• InnoDB存储引擎并不会回滚大部分的错误异常，但是死锁除外

• 死锁发生的概率见书p280

• InnoDB存储引擎不存在锁升级的问题，因为它不是根据每个记录来产生行锁的，相反，是根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式，因此锁的升级是没有意义的。