数据库核心技术深度剖析：事务、索引、锁与SQL优化实战指南（第四节）----从行级锁到死锁处理的系统梳理-海口c网

Introduction：收纳技术相关的数据库知识 事务、索引、锁、SQL优化 等总结！

文章目录

数据库锁
- 行级锁(Row-Level)
- - 属性锁
  - - 共享锁(Shared Locks)
    - 排它锁(Exclusive Locks)
  - 锁实现方式
  - - Record Lock(记录锁)
    - Gap Lock(间隙锁)
    - Next-Key Lock(临键锁)
- 加锁机制
- - 乐观锁(Optimistic Lock)
  - 悲观锁(Pessimistic Lock)
- 锁问题
- - 死锁
  - - 避免死锁
    - 预防死锁
    - 解决死锁
  - 脏读
  - 不可重复读
  - 幻读
  - 丢失更新

数据库锁

行级锁(Row-Level)

行级锁是MySQL中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突，其加锁粒度最小，但加锁的开销也最大。行级锁分为：

共享锁
排他锁

特点：开销大、加锁慢、会出现死锁、发生锁冲突的概率最低、并发度也最高。

在 InnoDB 事务中，行锁通过给索引上的索引项加锁来实现。只有通过索引条件检索数据，才使用行级锁，否则将使用表锁。行级锁定同样分为两种类型：共享锁 和 排他锁，以及加锁前需要先获得的 意向共享锁 和 意向排他锁。行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

行锁实现算法(3种锁都是排它锁(X锁))：

Record Lock(记录锁)
Gap Lock(间隙锁)
Next-Key Lock(临键锁)

属性锁

按照锁的共享策略来分：

共享锁（S锁，Shared Locks）
排它锁（X锁，Exclusive Locks）

请求锁模式是否兼容当前锁模式	X锁	IX锁	S锁	IS锁
X锁	`冲突`	`冲突`	`冲突`	`冲突`
IX锁	`冲突`	兼容	`冲突`	兼容
S锁	`冲突`	`冲突`	兼容	兼容
IS锁	`冲突`	兼容	兼容	兼容

若一个事务请求锁模式与当前锁兼容，InnoDB就将请求锁授予该事务；反之，两者不兼容，该事务就要等待锁释放。

共享锁(Shared Locks)

共享锁（ S锁 或 读锁）是指针对同一份数据，多个读操作可以同时进行而不会互相影响。若事务T对数据对象A加上S锁，则事务T只能读A；其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。加锁方式：

select ... lock in share mode

注意：

共享锁都是行锁

排它锁(Exclusive Locks)

**排它锁（ X锁 或 写锁）是指当前写操作没有完成前，它会阻断其它写锁和读锁。**若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它事务都不能再对A加任何类型的锁，直到T释放A上的锁。加锁方式：

insert
update
delete
select ... for update

注意

在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁

排它锁可以是行锁也可以是表锁

-- 当username是主键时，锁定的是行锁；当username不是主键时，是表锁
SELECT count(*) as total FROM test WHERE username = "zhangsan" FOR UPDATE

锁实现方式

Record Lock(记录锁)

为单个行记录上的锁，总是会去锁住索引记录。

-- id 列为主键列或唯一索引列
SELECT * FROM t_user WHERE id = 1 FOR UPDATE;

id 为 1 的记录行会被锁住。需要注意：

id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁
同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁

也可以在通过 主键索引 与 唯一索引 对数据行进行 UPDATE 操作时，也会对该行数据加记录锁：

-- id 列为主键列或唯一索引列
UPDATE t_user SET age = 50 WHERE id = 1;

注意：在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种：

如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引
如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引

Gap Lock(间隙锁)

间隙锁，想一下幻读的原因，其实就是行锁只能锁住行，但新插入记录这个动作，要更新的是记录之间的“间隙”。所以加入间隙锁来解决幻读。

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁基于下面将会提到的Next-Key Locking 算法，请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

SELECT * FROM t_user WHERE id BETWEN 1 AND 10 FOR UPDATE;
-- 或
SELECT * FROM t_user WHERE id > 1 AND id < 10 FOR UPDATE;

即所有在（1，10）区间内的记录行都会被锁住，所有id 为 2、3、4、5、6、7、8、9 的数据行的插入会被阻塞，但是 1 和 10 两条记录行并不会被锁住。除了手动加锁外，在执行完某些 SQL后，InnoDB也会自动加间隙锁。

幻读原因：因为行锁只能锁住行，但新插入记录这个动作，要更新的是记录之间的“间隙”。所以加入间隙锁来解决幻读。

间隙锁的目的

防止幻读，以满足相关隔离级别的要求
满足恢复和复制的需要

产生间隙锁的条件（RR事务隔离级别下）

使用普通索引锁定
使用多列唯一索引
使用唯一索引锁定多行记录

Next-Key Lock(临键锁)

Next-Key Lock(Record Lock + Gap Lock)锁定的是一个范围，并且锁定记录本身，MySql 防止幻读就是使用此锁实现。

临键锁是一种特殊的间隙锁，也可以理解为一种特殊的算法。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

比如：表信息 t_user(id PK, age KEY, name)
在这里插入图片描述

该表中 age 列潜在的临键锁有：

在这里插入图片描述

在事务 A 中执行如下命令：

-- 根据非唯一索引列 UPDATE 某条记录
UPDATE t_user SET name = Vladimir WHERE age = 24;
-- 或根据非唯一索引列 锁住某条记录
SELECT * FROM t_user WHERE age = 24 FOR UPDATE;

不管执行了上述 SQL 中的哪一句，之后如果在事务 B 中执行以下命令，则该命令会被阻塞：

INSERT INTO t_user VALUES(100, 26, 'tian');

很明显，事务 A 在对 age 为 24 的列进行 UPDATE 操作的同时，也获取了 (24, 32] 这个区间内的临键锁。

不仅如此，在执行以下 SQL 时，也会陷入阻塞等待：

INSERT INTO table VALUES(100, 30, 'zhang');

那最终我们就可以得知，在根据非唯一索引 对记录行进行 UPDATE、FOR UPDATE、LOCK IN SHARE MODE 操作时，InnoDB 会获取该记录行的 临键锁 ，并同时获取该记录行下一个区间的间隙锁。即事务 A在执行了上述的 SQL 后，最终被锁住的记录区间为 (10, 32)。

加锁机制

乐观锁(Optimistic Lock)

乐观锁的特点先进行业务操作，不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。

乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号或时间戳。乐观锁的两种实现方式：

使用数据版本（version）记录对比机制

当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。
使用时间戳（timestamp）记录对比机制

在更新提交时检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，一致则OK，否则为版本冲突。

悲观锁(Pessimistic Lock)

悲观锁（一锁二查三更新）的特点是先获取锁，再进行业务操作，即“悲观”的认为获取锁是非常有可能失败的，因此要先确保获取锁成功再进行业务操作。在数据库上的悲观锁需要数据库本身提供支持，即通过常用的 select … for update 操作来实现悲观锁。

MySQL还有个问题是 select... for update 语句执行中，如果数据表没有添加索引或主键，所有扫描过的行都会被锁上，这一点很容易造成问题。因此如果在MySQL中用悲观锁务必要确定走了索引，而不是全表扫描。

锁问题

死锁

MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

当两个及以上的事务，双方都在等待对方释放已经持有的锁或因为加锁顺序不一致造成循环等待锁资源，就会出现“死锁”。常见的报错信息为 ” Deadlock found when trying to get lock...”。

避免死锁

三种常见的避免死锁方式：

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率

预防死锁

innodb_lock_wait_timeout 等待锁超时回滚事务

直观方法是在两个事务相互等待时，当一个等待时间超过设置的某一阀值时，对其中一个事务进行回滚，另一个事务就能继续执行。
wait-for graph算法来主动进行死锁检测

每当加锁请求无法立即满足需要并进入等待时，wait-for graph算法都会被触发。wait-for graph要求数据库保存以下两种信息：
- 锁的信息链表
- 事务等待链表

解决死锁

等待事务超时，主动回滚
进行死锁检查，主动回滚某条事务，让别的事务能继续走下去

下面提供一种方法，解决死锁的状态:

-- 查看正在被锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

在这里插入图片描述

--上图trx_mysql_thread_id列的值
kill trx_mysql_thread_id;

脏读

脏读指的是不同事务下，当前事务可以读取到另外事务未提交的数据。

例如：T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
在这里插入图片描述

不可重复读

不可重复读指的是同一事务内多次读取同一数据集合，读取到的数据是不一样的情况。

例如：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
在这里插入图片描述

在 InnoDB 存储引擎中：

SELECT：操作的不可重复读问题通过 MVCC 得到了解决的
UPDATE/DELETE：操作的不可重复读问题是通过 Record Lock 解决的
INSERT：操作的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的

幻读

幻读是指在同一事务下，连续执行两次同样的 sql 语句可能返回不同的结果，第二次的 sql 语句可能会返回之前不存在的行。幻影读是一种特殊的不可重复读问题。

丢失更新

一个事务的更新操作会被另一个事务的更新操作所覆盖。

例如：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
在这里插入图片描述

这类型问题可以通过给 SELECT 操作加上排他锁来解决，不过这可能会引入性能问题，具体使用要视业务场景而定。