InnoDB——详细解释锁的应用，一致性读，自增长与外键

最新推荐文章于 2024-07-23 17:52:34 发布

zxu_er

最新推荐文章于 2024-07-23 17:52:34 发布

阅读量535

点赞数

分类专栏：读书笔记文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/zekser/article/details/129772237

版权

读书笔记专栏收录该内容

42 篇文章 2 订阅

订阅专栏

一致性非锁定读

一致性的非锁定读（consistent nonlocking read）是指InnoDB存储引擎通过行多版本控制的方式读取当前执行时数据库中行的数据。
如果读取的行正在执行行Delete或Update操作，这时读取操作不会因此去等待行上锁的释放。相反，InnoDB会去读行的一个快照数据。

之所以称为非锁定读，因为不需要等地访问的行上X锁的释放。
快照数据是指该行的之前版本的数据，通过undo段来实现的。而undo用来在事务中回滚数据，因此快照数据本身时没有额外的开销。
此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

因此，非锁定读机制极大地提高了数据库的并发性。InnoDB存储引擎默认采取这种读取方式，即读取不会占用和等待表上的锁。
但是在不同事务隔离级别下，读取的方式不同，并不是在每个事务隔离级别下都采用非锁定的一致性读。
此外，即使都是使用非锁定读一致性读，但是对于快照数据的定义也各不相同。

我们知道，快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本，一般称这种技术为行多版本技术。由此带来的并发控制称为：多版本并发控制（Multi Version Concurrency Control, MVCC）。

隔离级别和非锁定的一致性读

在事务隔离级别READ COMMITED 和 REPEATABLE READ（InnoDB的默认事务隔离级别）下，InnoDB使用非锁定的一致性读。然而，对于快照数据的定义却不同。
在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。
而在REPEATABLE READ级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

一致性锁定读

上面讲了，在默认隔离级别REPEATABLE READ下，InnoDB的SELECT操作使用一致性非锁定读。
但在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证逻辑的一致性。
而这要求数据库支持加锁语句，即使是对于SELECT的只读操作也可以加锁。
InnoDB支持两种一致性的锁定读（locking read）操作：

SELECT … FOR UPDATE
SELECT … LOCK IN SHARE MODE

SELECT … FOR UPDATE 对读取的行记录加一个X锁，其他事务不能对已锁定读行加上任何锁。
SELECT … LOCK IN SHARE MODE 对读取的行记录加一个S锁，其他事务可以向北锁定的行加S锁，但是如果该行已经加了X锁，则会被阻塞。

对于一致性非锁定读，即使读取的行已被执行了SELECT … FOR UPDATE，也是可以进行读取的（通过快照）。
此外 SELECT … FOR UPDATE 和 SELECT … LOCK IN SHARE MODE必须在一个事务中，当事务提交了，锁也就释放了。
因此在使用上述两句SELECT锁定语句时，务必加上BEGIN，START TRANSACTION或者SET AUTOCOMMIT=0。

自增长与锁

自增在在数据库中时非常常见的一种属性，也是很多开发人员首选的主键方式。

在InnoDB的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。
当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式被称做AUTO-INC Locking。

这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

虽然AUTO-INC Locking从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。
首先对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成（虽然不用等待事务的完成）。
其次，对于INSERT … SELECT的大量数据的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

从MySQL 5.1.22版本开始，InnoDB存储引擎中提供了一种轻量级互斥的自增长机制，这种机制大大提高了自增长值插入的性能。且从该版本开始，InnoDB存储引擎提供了一个参数innodb_automic_lock_mode来控制自增长的模式，该参数的默认值为1。
在继续讨论自增长实现方式之前，先对自增长的插入进行分类。

插入类型	说明
insert-like	只所有的插入语句，如INSERT，REPLACE、LOAD DATA等等
simple inserts	只能在插入前就确定插入行树的语句。包括：INSERT、REPLACE等。不包括INSERT … ON DUPLICATE KEY UPDATE这类SQL语句
bulk inserts	指在插入前不能确定得到插入行数的语句，如INSERT … SELECT, REPLACE … SELECT, LOAD DATA
mixed-mode inserts	指插入中有一部分是自增长，一部分是确定的。如：INSERT INTO t1(c1,c2) VALUES(1, ‘a’), (NULL,‘b’),(5,‘c’)；也可以是指INSERT … ON DUPLICATE KEY UPDATE这类SQL语句

接下来分析innodb_autoinc_lock_mode以及各个设置下对自增的影响，其总共有三个有效值可供设定，即0、1、2，具体如下所示：

为0时：

MySQL5.1.22之前的实现方式，通过表锁AUTO-INC Locking方式。因为有了新的自增长实现方式，0这个选项不应该是新版用户的首选项。

为1时：

这是默认值。对于“simple inserts”，该值会用互斥量（mutex）去对内存中的计数器进行累加的操作。对于“bulk inserts”，还是使用传统表锁的AUTO-INC Locking方式。在这种配置下，如果不考虑回滚操作，对于自增值列的增长还是连续的。

注意，如果已经使用AUTO-INC Locing方式去产生自增长的值，而这时需要再进行“simple inserts”的操作时，还是需要等待AUTO-INC Locking的释放

为2时

该模式下，对于所有“INSERT-like”自增长值的产生都是通过互斥量，而不是AUTO-INC Locking方式，该模式下性能最高。
然而因为并发插入的存在，在每次插入时，自增长可能不是连续的。最重要的是，基于Statement-Base Replication会出现问题。因此，使用这个模式，任何时候都应该使用row-base replication。这样才能保证最大的并发性能及replication主从数据的一致。

注意事项：

MyISAM与InnoDB的区别
还需要特别注意InnoDB中自增长的实现和MyISAM不同，MyISAM是表锁设计，自增长不用考虑并发插入的问题。
因此在master上用InnoDB存储引擎，在slave上用MyISAM存储引擎的replication架构下，用户必须考虑这种情况。

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

外键和锁

外键主要用于引用完整性的约束检查。
在InnoDB中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB会自动对其加一个索引，因为这样可以避免表锁。而Oracle不会自动增加索引，用户必须自己手动添加，这也导致了Oracle数据库中可能会产生死锁。

对于外键值的插入或更新，首先需要查询父表中的记录，即SELECT父表。
但是对于父表的SELECT操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题。
因此这时使用的是SELECT … LOCK IN SHARE MODE方式，即主动对浮标加一个S锁。如果这时父表上已经这样加X锁，子表上的操作会被阻塞。

参考了这里