Designer Watches: InnoDB 存储引擎中的锁.

2021-01-05

InnoDB 存储引擎中的锁.

一、锁的类型

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

InnoDB 存储引擎实现了如下两种标准的行级锁，其中，X 锁与任何的锁都不兼容，而 S 锁仅和 S 锁兼容（兼容指对同一记录行的兼容性情况）

共享锁（S Lock），允许事务读一行数据；
排他锁（X Lock），允许事务删除或更新一行数据；

InnoDB 存储引擎除了行锁以外，还有表锁，通常也称为意向锁，其设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

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

通过 information_schema 架构下的 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 三张表，用户可以更简单的监控当前事务并分析可能存在的锁问题。

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

二、锁的算法

InnoDB 存储引擎有三种行锁的算法，其分别是：

Record Lock：单个行记录上的锁
Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
Next-Key Lock：Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身

Record Lock 总是会去锁住索引记录，InnoDB 存储引擎会使用聚簇索引来进行锁定。

Gap Lock 的作用是为了阻止多个事务将记录插入到同一个范围内，因为这会导致幻读问题（phantom Problem）的产生，用户可以通过以下两种方式来显式地关闭 Gap Lock：

将事务的隔离级别设置为 READ COMMITTED
将参数 innodb_locks_unsafe_for_binlog 设置为 1

Next-Key Lock 是结合了 Gap Lock 和 Record Lock 的一种索引算法，这种锁定技术，不止锁定记录本身，还锁定一个范围。

InnoDB 对于行的查询默认是采用 Next-Key Lock 算法，当查询的索引含有唯一属性时（主键索引、唯一索引），InnoDB 存储引擎会对 Next-Key Lock 进行优化，将其降级为 Record Lock；而对于辅助索引，不仅会对索引列加 Record Lock ，还会对索引列前后的键值范围加上 Gap Lock。

Phantom Problem：幻读问题，指在同一事务下，连续执行两次同样的 SQL 语句可能导致不同的结果，第二次的 SQL 语句可能会返回之前不存在的行。

1. 聚簇索引的加锁情况

select * from students where id = 20 for update;

2. 唯一索引的加锁情况

select * from students where num = 135 for update;

3. 二级索引的加锁情况

select * from students where score= 91 for update;

4. 无索引的加锁情况

select * from students where age = 22 for update;

三、锁的问题

Dirty Read 脏读：一个事务读到了另一个未提交的事务写的数据，这显然违反了数据库的隔离性。
Non-Repeatable Read 不可重复读：一个事务内多次读取同一数据集合，可能两次读到的数据是不一样的。不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据，这显然违反了数据库的一致性。
Phantom Problem 幻读：幻读问题，指在同一事务下，连续执行两次同样的 SQL 语句可能导致不同的结果，第二次的 SQL 语句可能会返回之前不存在的行。幻读是比不可重复读高一个级别的错误，读取同一条数据发现跟刚才是一样的，只有读取一堆数据发现忽然多了一个，或者少了一个，像是产生了幻觉。
Lost Update 更新丢失
a. 第一类更新丢失，回滚覆盖：撤消一个事务时，在该事务内的写操作要回滚，把其它已提交的事务写入的数据覆盖了。
b. 第二类更新丢失，提交覆盖：提交一个事务时，写操作依赖于事务内读到的数据，读发生在其他事务提交前，写发生在其他事务提交后，把其他已提交的事务写入的数据覆盖了。这是不可重复读的特例。

为了解决多个事务并发会引发的锁问题，数据库系统提供了四种事务隔离级别供用户选择。

Read Uncommitted 读未提交：不允许第一类更新丢失。允许脏读，不隔离事务。
Read Committed 读已提交：不允许脏读，允许不可重复读（即允许第二类更新丢失）。
Repeatable Read 可重复读：不允许不可重复读（即不允许第二类更新丢失），但可能出现幻读。
Serializable 串行化：所有的增删改查串行执行。

为什么 MYSQL 默认使用 Repeatable Read 隔离级别？这跟数据库的主从复制有关，MYSQL 的主从复制是基于 binlog 复制的，而 binlog 有三种格式，分别为：

statement：记录的是修改 SQL 语句
row：记录的是每行实际数据的变更
mixed：statement 和 row 模式的混合

那 MYSQL 在 5.0 这个版本以前，binlog 只支持 statement 这种格式！而这种格式在读已提交（Read Commited）这个隔离级别下主从复制是有 bug 的，因此 Mysql 将可重复读（Repeatable Read）作为默认的隔离级别。

怎么解决 Read Committed 隔离级别下，主从复制有问题的 bug？首先得解释下这个 bug，在 master 上执行的顺序为先删后插，若此时 binlog 为 statement 格式，它记录的顺序为先插后删，slave 同步的是 binglog，因此 slave 执行的顺序和主机不一致，就会出现主从不一致，怎么解决这个 bug 呢？

隔离级别设为可重复读（Repeatable Read），在该隔离级别下引入间隙锁（GAP LOCK），在执行 DELETE 语句时，会锁住间隙，那么执行 INSERT 语句就会阻塞住。
将 binglog 的格式修改为 row 格式，此时是基于行的复制，自然就不会出现 sql 执行顺序不一样的问题（这个格式在 MYSQL 5.1 版本才开始引入）。

四、其它

在 InnoDB 存储引擎中，参数 innodb_lock_wait_timeout 用来控制等待得时间（默认是 50 秒），innodb_rollback_on_timeout 用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是 OFF，代表不回滚）
InnoDB 存储引擎在大部分情况下都不会对异常进行回滚（死锁除外），因此用户必须判断是否需要 COMMIT 还是 ROLLBACK，之后再进行下一步的操作。
InnoDB 存储引擎通过 wait-for graph（等待图）的方式来进行死锁检测，wait-for graph 是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说 InnoDB 存储引擎选择回滚 undo 量最小的事务。
InnoDB 存储引擎不存在锁升级的问题，因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管一个事务锁住页中的一个记录还是多个记录，其开销通常是一致的。

原文转载：http://www.shaoqun.com/a/505917.html

跨境电商：https://www.ikjzd.com/

吉祥邮：https://www.ikjzd.com/w/1565

krazy：https://www.ikjzd.com/w/1801

一、锁的类型InnoDB存储引擎lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行，并且一般lock的对象仅在事务commit或rollback后进行释放（不同事务隔离级别释放的时间可能不同）。InnoDB存储引擎实现了如下两种标准的行级锁，其中，X锁与任何的锁都不兼容，而S锁仅和S锁兼容（兼容指对同一记录行的兼容性情况）共享锁（SLock），允许事务读一行数据；排他锁（XLock），
moss：moss
sca：sca
2020年(第十七届)海南国际旅游岛欢乐节有优惠活动景区介：2020年(第十七届)海南国际旅游岛欢乐节有优惠活动景区介
【哈尔滨旅游】—哈尔滨太阳岛景区最佳旅游季节 - ：【哈尔滨旅游】—哈尔滨太阳岛景区最佳旅游季节 -
亚马逊防止跟卖的几种办法：亚马逊防止跟卖的几种办法

Designer Watches