数据库系列：MySQL不同操作分别用什么锁？

数据库系列：MySQL慢查询分析和性能优化
数据库系列：MySQL索引优化总结（综合版）
数据库系列：高并发下的数据字段变更
数据库系列：覆盖索引和规避回表
数据库系列：数据库高可用及无损扩容
数据库系列：使用高区分度索引列提升性能
数据库系列：前缀索引和索引长度的取舍
数据库系列：MySQL引擎MyISAM和InnoDB的比较
数据库系列：InnoDB下实现高并发控制
数据库系列：事务的4种隔离级别
数据库系列：RR和RC下，快照读的区别
数据库系列：MySQL InnoDB锁机制介绍

1 回顾

我们之前讲了那么多，包括 数据库系列：InnoDB下实现高并发控制 和 数据库系列：MySQL InnoDB锁机制介绍。

总结一下，在MySQL的InnoDB存储引擎中，可以使用以下几种锁来保护并发操作：

  1. 共享锁（Shared Lock\S锁）：

共享锁允许多个事务同时读取同一行数据，但不允许对其进行修改。如果一个事务获取了一行数据的共享锁，其他事务也可以同时获取同一行数据的共享锁，但是任何尝试获取该行数据排他锁（即进行修改操作）的事务都将被阻塞，直到共享锁被释放。
总之，共享锁不互斥，多个事务可以同时获取同一行数据的共享。我们简记为：读读并行。

# 加锁语句
SELECT ... FOR SHARE
  1. 排他锁（Exclusive Lock\X锁）：

排他锁也称为写锁，它允许一个事务独占地对一行数据进行修改，其他事务无法同时获取该行的共享锁或排他锁。使用排他锁的事务可以确保在修改数据期间，没有其他事务能够读取或修改该行数据。
总之，排他锁互斥，同一行数据只能被一个事务获取排他锁，与其他任何锁互斥。我们简记为：写读、写写阻塞。

# 加锁语句
SELECT ... FOR UPDATE
  1. 意向锁（Intent Lock）：

意向锁是一种低级别的锁，用于表示事务意图对某个数据范围进行锁定。它可以是共享锁或排他锁的意图表示。意向锁的作用是避免其他事务在范围级别上进行修改操作，而具体的行级锁定则由应用程序根据需求自行决定。
加锁语句：在执行UPDATE或DELETE语句之前，可以使用如下语句获取意向锁。

SELECT ... FOR UPDATE
# 或
SELECT ... FOR SHARE
  1. 悲观锁（Pessimistic Lock）：

悲观锁假设最坏的情况，即在执行每个数据修改操作前都会先获取排他锁。这种锁的策略下，事务在修改数据时会先锁定该行数据，确保其他事务无法同时修改该行数据。乐观锁则假设最好的情况，即数据不会被其他事务同时修改，因此只在提交数据修改时才检查是否有冲突。

  1. 乐观锁（Optimistic Lock）：

乐观锁在执行数据修改操作时不会先锁定数据，而是在提交修改时检查是否有冲突。如果检测到冲突，则事务会回滚并重新尝试。乐观锁适用于读多写少的应用场景，可以提高并发性能。
加锁语句：乐观锁没有特定的加锁语句，而是通过版本号机制来实现。在执行UPDATE或DELETE操作时，InnoDB会检查数据的版本号是否与最初读取到的版本号一致，如果不一致则表示有其他事务修改了该数据，此时会回滚事务并抛出异常。

需要注意的是，InnoDB还支持自动提交（AUTOCOMMIT）和显式提交（COMMIT）来控制事务的提交和回滚。在使用InnoDB时，可以通过调整隔离级别和并发控制参数来优化并发性能和数据一致性。

2 DML和Select具体使用什么锁

2.1 普通Select

  1. 我们之前说过了，普通查询为什么快，支持并发执行，不阻塞其他操作，主要是使用了快照读(snpashot read).

这个模式在 读未提交(Read Uncommitted/RU) 和 读已提交(Read Committed/RC)、可重复读（Repeated Read/RR） 隔离级别下都是有效的。

参考作者这篇：数据库系列：InnoDB下实现高并发控制

  1. 另外一种隔离级别：串行化（Serializable），也是我们之前说过的，在InnoDB中，串行化是最高隔离级别，普通select会升级为select ... in share mode。

参考作者这篇：数据库系列：事务的4种隔离级别

2.2 加锁的select

加锁的select，会使用排他锁（X锁）来保护数据，参照我们上面的介绍，主要有如下几种情况：

# 排他场景
selet ... for update

# 共享场景
select ... for share mode

这时候有如下情况：

  • 在主键（primary key）和 唯一索引(unique index)上使用唯一的查询条件(unique search condition)，会使用记录锁(record lock)，即行锁
  • 如果条件为记录区间，则会封锁记录之间的间隔，即使用间隙锁(gap lock)与临键锁(next-key lock)

参考作者这篇：数据库系列：MySQL InnoDB锁机制介绍

2.2.1 记录锁

以例为证：

# 表结构
table (Id PK, Name , Company);

# 表中包含四条记录
5, Gates, Microsoft
7, Bezos, Amazon
11, Jobs, Apple
14, Elison, Oracle

记录锁，它封锁索引记录，例如：

select * from table where id=5 for update;

它会在id=1的索引记录上加锁，以阻止其他事务插入，更新，删除id=1的这一行。

2.2.2 间隙锁/临键锁

区间范围的查询条件和索引条件，InnoDB会封锁被扫描的索引范围，并使用间隙锁与临键锁，避免索引范围区间插入记录
以例为证：

select * from table
where id between 7 and 13 
for update;

这样的话，会封锁数据的区间，以防止其他事务 插入\修改\删除 id=8的记录。

2.3 Update/Delete 操作

  1. 和select加锁同理， 唯一索引(unique index)上使用唯一的查询条件(unique search condition)，会使用记录锁(record lock)，即行锁

举例：

# 只会锁住id=1的这一行
update table set name='Brand' where id=5;

  1. 区间范围情况，符合查询条件的索引记录范围，都会加排他临键锁(exclusive next-key lock)，来封锁索引记录区间，来避免其他事务插入，更新，删除该区间内的索引记录。

  2. 如果update的是聚集索引(clustered index)记录，则对应的普通索引(secondary index)记录也会被隐式加锁，这是由InnoDB索引的实现机制决定的。

Innodb中最好是采用主键索引查询，这样只需要一次索引，如果使用辅助索引检索，涉及多一步的回表操作，比主键查询要耗时一些。
所以，InnoDB的普通索引，实际上会扫描两遍：
第1遍，由普通索引找到PK：检索到name='Ellison'的数据，获取id为14
第2遍，由PK找到行记录：即到主键索引中检索id为14的记录
image

对索引有兴趣的，可以参考作者的这几篇文章：
MySQL全面瓦解22：索引的介绍和原理分析
MySQL全面瓦解23：MySQL索引实现和使用
MySQL全面瓦解24：构建高性能索引（策略篇）

2.4 Insert 操作

Insert操作和Update/Delete操作不同，排它锁封锁的只是新插入的索引记录，而不会封锁记录之前的范围。
同理，会在插入区间加插入意向锁(insert intention lock)，但这个并不会真正封锁区间，也不会阻止相同区间的不同KEY的写入。

3 总结

本文介绍了在MySQL的InnoDB存储引擎中，如何通过几种类型的锁来保护并发操作。
以及不同SQL语句使用锁的情况，这对于分析多个事务之间的并发与互斥，以及事务死锁，是非常有帮助的。

posted @ 2023-12-06 14:30  Hello-Brand  阅读(665)  评论(0)  编辑  收藏  举报