InnoDB 锁

【什么是锁】

锁是数据库系统区别于文件系统的一个关键特性。 锁机制用于管理对共享资源的并 发访问。InnoDB存储引擎会在行级别上对表数据上锁，这固然不错。不过InnoDB存

储引擎也会在数据库内部其他多个地方使用锁，从而允许对多种不同资源提供并发访 问。例如，操作缓冲池中的LRU列表，删除、添加、移动LRU列表中的元素，为了保证一致性，必须有锁的介人。数据库系统使用锁是为了支持对共享资源进行并发访问提供数据的完整性和一致性。另--点需要理解的是，虽然现在数据库系统做得越来越类似，但是有多少种数据库，就可能有多少种锁的实现方法。在SQL语法层面，因为SQL标准的存在，要熟悉多个关系数据库系统并不是一件难事。而对于锁，用户可能对某个特定的关系数据库系统的锁定模型有一定的经验，但这并不意味着知道其他数据库。在使用nnoDB存储引擎之前，我还使用过MySQL数据库的MyISAM和NDB Cluster存储引擎。在使用MySQL数据库之前，我还曾经使用过Microsoft SQL Server、Oracle等数据库，但它们MySQL数据库之前，我还曾经使用过Microsoft SQL Server、Oracle等数据库，但它们各自对于锁的实现完全不同。

对于MyISAM引擎，其锁是表锁设计。并发情况下的读没有问题，但是并发插人时的性能就要差一些了，若插人是在“底部”，MyISAM存储引擎还是可以有一定的并发写入操作。对于Microsoft sQL Server数据库，在Microsoft SQL Server 2005版本之前其都是页锁的，相对表锁的MyISAM引擎来说，并发性能有所提高。页锁容易实现，然而对于热点数据页的并发问题依然无能为力。到2005版本，Microsoft SQL Server开始支持乐观并发和悲观并发，在乐观并发下开始支持行级锁，但是其实现方式与InnoDB存储引擎的实现方式完全不同。用户会发现在Microsoft SQL Server下，锁是一种稀有的资源，锁越多开销就越大，因此它会有锁升级。在这种情况下，行锁会升级到表锁，这时并发的性能又回到了以前。

InnoDB存储引擎锁的实现和Oracle数据库非常类似，提供一致性的非锁定读、行 级锁支持。行级锁没有相关额外的开销，并可以同时得到并发性和---致性。

【lock与latch】

这里还要区分锁中容易令人混淆的概念lock与latch。在数据库中，lock与latch都可以被称为“锁”。 但是两者有着截然不同的含义，本章主要关注的是lock。

【InnoDB的锁实现】

事务隔离级别只决定看不看得见。

首先事务不等于sql语句，一个事务可以有多个sql语句，一条sql语句不用显示开启事务因为它执行完自动提交事务。两个事务并发时，两个事务里面的sql语句如果对某行数据有冲突，如何处理冲突是看事务隔离级别来处理这种冲突的：

* 读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。

* 读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。

* 可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

* 串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

脏读： 事务A读到了事务B还没有提交的数据。解决： 读未提交 升级为 读已经提交。

不可重复读： 在一个事务里面读取了两次某个数据，读出来的数据不一致，读到了别的事务提交的修改。解决： 已提交 升级为 可重复读

幻读： 在一个事务里面的两次select中发现了凭空出现的数据，幻读只针对插入因为数据不在则不能加锁，不能加锁就能插入，插入后select比插入前select得到的数据多。解决： 可重复读 升级为  串行操作 。innodb通过Next-Key lock解决了可重复读级别下的幻读问题。

丢失更新：

两个事务修改同一个数据，然后按顺序提交，后提交的会覆盖先提交的。当然在4个隔离级别下数据库都不会有这种问题。这种问题通常出现在数据库用户的逻辑层。通常要求用户手动对数据进行手动加锁，通过底层数据库的加锁来曲线救国。

解决方法就是查询的用户余额数据手动加X锁，然后再更新，来串行化执行：

InnoDB存储引擎实现了如下两种标准的行级锁:

共享锁（S Lock)，允许事务读一行数据。

排他锁（XLock)，允许事务删除或更新一行数据。

如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容（Lock Compatible

但若有其他的事务T3想获得行r的排他锁，则其必须等待事务T1、T2释放行r上的共 享锁——这种情况称为锁不兼容。

从表6-3可以发现X锁与任何的锁都不兼容，而S锁仅和S锁兼容。需要特别注意的是，S和锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。

意向锁：

1）意向共享锁（IS Lock)，事务想要获得一张表中某几行的共享锁

2）意向排他锁（IX Lock)，事务想要获得一张表中某几行的排他锁

InnoDB可以支持多粒度加锁，称之为意向锁，被锁的对象可以从粗到细划分层次。意向锁粒度比普通锁粒度要大。

若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象 进行上锁，那么首先需要对粗粒度的对象上锁。例如图6-3， 如果需要对页上的记录r进 行上X锁，那么分别需要对数据库A、表、页上意向锁IX，最后对记录r上X锁。若其中任何一个部分导致等待，那么该操作需要等待粗粒度锁的完成。举例来说， 在对记 录r加X锁之前，已经有事务对表1进行了S表锁，那么表1上已存在S锁，之后事务 需要对记录r在表1上加上IX，由于不兼容，所以该事务需要等待表锁操作的完成。

IS、IX、S、X四种锁的兼容性：

如何查看锁的请求：

一致性非锁定读：

InnoDB会通过多版本的方法，来读取当前执行时间数据库中 行的数据的快照版本。如果读取的行正在执 行DELETE或UPDATE操作，这 时读取操作不会因此去等待行上 锁的释放。相反地，InnoDB存储 引擎会去读取行的--个快照数据。

根据名字，可知道在读取的时候不需要等待行上面的X锁的释放 。 快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上 锁的，因为没有事务需要对历史的数据进行修改操作。

基于版本就是MVVC，多版本并发控制肯定布置一个数据快照而是有多个版本，到底读取哪一个版本是根据事务隔离级别来的：

RC[读以提交]：一致性非锁定读是读取数据最新的快照版本。

RR[innodb默认级别，可重读]：一致性非锁定读是读取当前事务开始时的快照版本。

一致性锁定读：

默认的事务隔离级别下RR，默认select的读都是一致性非锁定读取事务开始时的版本。用户可以强制要求使用一致性锁定读，相当于用户强制加锁保证数据逻辑一致性，innodb支持两种锁定读：

SELECT…FOR UPDATE：对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁

SELECT…LOCK IN SHARE MODE：对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。

根据名字语义可以看出。

for update就是强制要求现在只有当前事务能改这条数据，其他事务不能上读锁，不能上写锁。但是一致性非锁定读可以读取因为它没有上锁。

share mode就是强制要求现在只有当前事务能改这条数据，其他事务能上读锁，不能上写锁。

当事务提交了，锁也就释放了。因此在使用上述两句SELECT锁定语句时，务必加上BEGIN，START TRANSACTION或者SETAUTOCOMMIT=O。

下列情况分析：

两个session对同一行执行for update：仅一个能成功，另外一个直接失败。

两个session对同一行执行share mode：都能成功。

两个session对同一行先后执行for update和share mode：前者成功，后者直接失败。

两个session对同一行先后执行 share mode和 for update：前者成功，后者阻塞等待。

【自增长与锁】

主键自增，插人操作会依据这个自增长的计数器值加1赋予自增长列。 这个实现方式称做 AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插人的性能， 锁 不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。即这个+1的表锁，在sql执行完直接释放，不等待这个事务最后完成。虽然AUTO-INC Locking从一定程度上提高了并发插人的效率，但还是存在-些性能上的问题。首先，对于有自增长值的列的并发插人性能较差 ，事务必须等待前一个插 人的完成（虽然不用等待事务的完成)。其次，对于INSERT…SELECT的大数据量的插人会影响插人的性能，因为另一个事务中的插人会被阻塞。

从MySQL 5.1.22版本开始，InnoDB存储引擎中提供了一种轻量级互斥量的自增实现机制，这种机制大大提高了自增长值插人的性能。并且从该版本开始，InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数的默认值为：

另外，在InnoDB存储引擎中，自增长值的列必须是索引，同时必须是索引的第聊个列。如果不是第一个列，则MySQL数据库会抛出异常，而MyISAM存储引擎没有这

个问题，下面的测试反映了这两个存储引擎的不同:

【外键和锁】

前面已经介绍了外键，外键主要用于引用完整性的约束检查。在InnoDB存储引擎中， 对于一个外键列，如果没有显式地对这个列加索引，InnoDB存储引擎自动对其加一 个索引，因为这样可以避免表锁-—这比Oracle数据库做得好，Oracle数据库不会自动添加索引，用户必须自己手动添加，这也导致了Oracle数据库中可能产生死锁。

因为外键在操作子表时，要检测父表中数据是否存在，但是这里不能使用一致性非锁定读，而是使用select for share mode，对父表家S锁，如果此时父表已经被加了X锁，则这里读取父表会被阻塞。

这里不能使用一致性非锁定读的原因可以用反证法，如果默认RC下会话B读取父表时读的事务开始时的一个快照备份，那么在会话A执行删除语句又不能阻止会话B的读取，最后AB会话都提交了，那么数据就不一致了相当于这个id=3的外键约束就失效了。

【锁算法】

Record Lock:单个行记录上的锁，有索引时锁索引，没索引的锁rowid。

Gap Lock:间隙锁，锁定一个范围，但不包含记录本身。不能锁数据所以有幻读问题。

Next-Key Lock : Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身。是记录锁和间隙锁的组合，innodb的默认查询都是这种锁。Next-key是左开右闭，previous-key是左闭右开。这种锁会按照已存在记录值生成不同分段区间的锁范围。解决了幻读。

如索引上有10、11、13、20共4条记录，就会生成 (-∞,10]，(10,11]，(11,13]，(13,20]，(20,+∞]。共五个锁区间。

Next-key锁实例：

当查询的索引不含唯一属性时。需要同时对该列数据的每个索引都加上NK锁。

例，创建表(a,b)，其中a为主键索引，b为普通索引[无唯一属性]。

同时执行下面的select语句：

那么会 将 所有b=3的数据 的 所有索引列都加上NK锁。这里会锁住b=(1,3]这个区间，也会锁住a=(3,5]这个区间。

Next-Key Lock退化为Record Lock锁：

当查询的索引含有唯一属性时，会退化成记录行锁以提高效率。

例：如果t表已存在索引a为1、2、5共三条记录，而且a为主键。那么锁范围共(-∞,1]，(1,2]，(2,5]，(5,+∞]共4个锁区间，而会话A的where条件会落在(2,5]这个区间，会话B的insert刚好在锁区间内。由于索引列a是主键即有[唯一属性]，则退化为行锁只锁住a=5这一行，会话A不会阻塞会话B。

Gap Lock和Next-Key Lock对比：

仅仅Gap的话只能锁定间隙的话比如(a,b)间隙，不能锁记录行因为记录行此时还没插入进来，那么insert a不在锁定范围能插入成功，这样会有幻读问题:

SELECT * FROM t WHERE a> 2 FOR UPDATE;

INSERT t SET a=4;

SELECT * FROM t WHERE a> 2 FOR UPDATE;

会发现在一个session事务里执行两次相同的select，会看到别的事务提交的数据造成前后读取数据不一样。

而Next-Key能解决幻读问题，锁住了数据区间来实现 当前读，即这条select for update能看到最新的commit，并对该记录加X锁，即当前读是用NKL实现的。

【死锁】

死锁只能通过超时时间来解决。立子AB-BA死锁，innodb在发现死锁时会回滚其中一个。

【锁升级】

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

InnoDB存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。假设一张表有3 000 000个数据页，每个页大约有100条记录，那么总共有300 000 o0o条记录。若有一个事务执行全表更新的SQL语句，则需要对所有记录加X锁。若根据每行记录产生锁对象进行加锁，并且每个锁占用10字节，则仅对锁管理就需要差不多

需要3GB的内存。而InnoDB存储引擎根据页进行加锁，并采用位图方式，假设每个页存储的锁信息占用30个字节，则锁对象仅需90MB的内存。由此可见两者对于锁资源开销的差距之大。