MySQL学习笔记：锁_read committed select for update-CSDN博客

本文链接：https://blog.csdn.net/u013066244/article/details/111051103

环境

MacBook Pro

序言

MySQL 技术内幕 InnoDB 存储引擎第二版

锁

数据库中最大的难点：一方面需要最大程度地利用数据库的并发访问，另外一方面还要确保每个用户能以一致的方式读取和修改数据。— 因此就有了锁的机制。

Q：InnoDB存储引擎为什么一个锁和多个锁的开销是相同。
A：

lock的对象是事务，用来锁定的是数据库中的对象，如：表、页、行。并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。

	lock
对象	事务
保护	数据库内容
持续时间	整个事务过程
模式	行锁、表锁、意向锁

锁的类型

MySQL有两种标准的行级锁：
① 共享锁（S Lock）允许事务读取一行数据；
② 排它锁（X Lock）允许事务删除或更新一行数据

排它锁和任何锁都不兼容，共享锁只和共享锁兼容

意向锁

MySQL为了支持多粒度锁定：这种锁定允许事务在行级上的锁和表级上的锁同时存在。

意向锁表示方式：IX；

举例：如果需要对页上的记录r进行上锁，那么分别需要对数据库A，表，页加意向锁IX，最后对记录r加X锁。

所以，意向锁就是表级锁；意向锁不会阻塞除全表扫描外的其他请求。

意向锁只会阻塞表级锁；
意向锁，和行级锁比较时，排他不同类型一定不兼容，都是共享类型那么意向和行锁都兼容。

Q: 举例说明为什么需要意向锁？

A: MySQL支持行级锁：共享锁和排它锁；
假设事务A，对某条记录r申请了S锁（共享锁），
事务B想修改表中多条数据，申请了整个表的写锁。
在没有意向锁之前，假设事务B申请成功了，那么理论上它就可以修改表中的任意一行，这与A持有的行锁冲突了。
数据库需要避免这样的冲突，就需要让事务B的申请被阻塞，直到A释放了锁。

怎么判断是否冲突了呢？

step1: 判断表是否已被其他事务用表锁锁住
step2: 判断表中的每行是否已被行锁锁住。

步骤②中，这种判断很低效。

有了意向锁后，
step1: 判断表是否已被其他事务用表锁锁住
step2: 发现表上有意向锁，说明表中有些行被共享锁锁住了，因此事务B申请表的写锁会被阻塞。

申请意向锁的动作是数据库完成的，不需要用代码来控制

Q：这里进一步思考，假设事务B申请的是也是某行记录，和事务A不是一行，那么会被阻塞吗？
A：不会，虽然事务A，对表加了意向锁IS，但是意向锁和行锁是兼容的，而事务B上的行锁又和事务A不是同一行，
所以不会被阻塞。

参考地址：InnoDB 的意向锁有什么作用？

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

一致性非锁定读

概念：InnoDB存储引擎通过行多版本控制的方式来读取当前执行时间数据库中的数据。

一致性非锁定读：InnoDB存储引擎，默认配置下的，默认的读取方式；

InnoDB默认的事务隔离级别：repeatable read;
repeatable read 隔离级别采用Next-Key Lock算法，避免了不可重复读。
Next-Key Lock算法：不仅锁住扫描到的索引，而且还锁住了这些索引覆盖的范围（gap）

在这里插入图片描述

如果读取的行正在执行delete或update操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB存储引擎会去读取行的一个快照数据。

快照数据是指行之前的版本的数据，该实现是通过undo段来完成的。而undo是用于事务中回滚数据的。因此快照数据本身没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改。

不同事务隔离级别对快照数据的定义是不同的：

read committed：非锁定一致性读，总是读取被锁定行的最新一份快照数据。
repeatable read：非锁定一致性读，总是读取事务开始时的行数据版本。

一致性锁定读

默认情况下，InnoDB 存储引擎的select操作使用一致性非锁定读。

但是在某些情况下，用户需要显示的对数据库读取操作进行加锁以保证数据逻辑的一致性。这就要求数据库支持加锁语句。即使对于select的只读操作，InnoDB存储引擎对于select语句支持两种一致性的锁定读：
① select … for update
② select … lock in share mode

select … for update和select … lock in share mode

select ... for update: 会对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。另外，需要注意的是：如果当前索引使用的是覆盖索引，那么它除了锁覆盖索引之外，还会锁主键索引。

select ... lock in share mode: 对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。另外，需要注意的是：如果当前索引使用的是覆盖索引，那么它只锁覆盖索引。

自增长与锁

对于每个含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下语句得到计数器的值：

select max(auto_inc_col) from t for update

插入操作会依据这个自增长的计数器的值加1，然后赋值给自增长列。这种实现方式叫auto-inc locking;(这是一种特殊的表锁机制）

为了提高性能，锁不是在事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。
也就是说：事务没释放之前，只要插入操作执行完成，锁就释放掉了。

锁的算法

锁的机制可以解决事务的隔离性要求。

InnoDB存储引擎锁的算法大致分类三种：

算法名称	描述	解决的问题
record lock	单个行记录	解决脏读的
gap lock	间隙锁，锁定一定范围，但是不包含记录本身	解决幻象问题
next-key lock	上面两个的结合；锁定一定的范围，并且锁定记录本身	解决幻象问题

讲解：
① record lock ：总是会锁住索引记录；如果表中没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键进行锁定。
② next-key lock锁的目的是为了解决幻象问题；（幻读和不可重复读）

对next-key lock举例说明：
假设索引中有10 、11、13、20
那么该算法会锁定的区间：
（10,11], (11,13]
当插入新的记录12时，则锁定的范围会变成：
(10, 11], (11, 12],(12,13]

说明：
① record lock 总是去锁定索引记录。如果没有创建索引，那么就使用主键来进行锁定。
② InnoDB对行的查询使用的就是next-key lock算法。
③ 当查询的索引含有唯一属性时，InnoDB存储引擎会对next-key lock 降级为Record Lock。即锁住索引本身而不是范围。