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;

三、锁的问题

  1. Dirty Read 脏读:一个事务读到了另一个未提交的事务写的数据,这显然违反了数据库的隔离性。

  2. Non-Repeatable Read 不可重复读:一个事务内多次读取同一数据集合,可能两次读到的数据是不一样的。不可重复读和脏读的区别是:脏读是读到未提交的数据,而不可重复读读到的却是已经提交的数据,这显然违反了数据库的一致性。

  3. Phantom Problem 幻读 :幻读问题,指在同一事务下,连续执行两次同样的 SQL 语句可能导致不同的结果,第二次的 SQL 语句可能会返回之前不存在的行。幻读是比不可重复读高一个级别的错误,读取同一条数据发现跟刚才是一样的,只有读取一堆数据发现忽然多了一个,或者少了一个,像是产生了幻觉。

  4. 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 呢?

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

四、其它

  1. 在 InnoDB 存储引擎中,参数 innodb_lock_wait_timeout 用来控制锁等待的时间(默认是 50 秒),innodb_rollback_on_timeout 用来设定是否在等待超时时对进行中的事务进行回滚操作(默认是 OFF,代表不回滚)

  2. InnoDB 存储引擎在大部分情况下都不会对异常进行回滚(死锁除外),因此用户必须判断是否需要 COMMIT 还是 ROLLBACK,之后再进行下一步的操作。

  3. InnoDB 存储引擎通过 wait-for graph(等待图)的方式来进行死锁检测,wait-for graph 是一种较为主动的死锁检测机制,在每个事务请求锁并发生等待时都会判断是否存在回路,若存在则有死锁,通常来说 InnoDB 存储引擎选择回滚 undo 量最小的事务。

  4. InnoDB 存储引擎不存在锁升级的问题,因为其不是根据每个记录来产生行锁的,相反,其是根据每个事务访问的每个页对锁进行管理的,采用的是位图的方式。因此不管一个事务锁住页中的一个记录还是多个记录,其开销通常是一致的。

posted @ 2021-01-05 10:55  JMCui  阅读(318)  评论(0编辑  收藏  举报