MySQL （五）锁

2024 年 4 月 21 日 星期日(已编辑)
/
49
这篇文章上次修改于 2024 年 7 月 17 日 星期三，可能部分内容已经不适用，如有疑问可询问作者。

MySQL （五）锁

详细说一下MySQL数据库中锁的分类

根据锁粒度的不同，MySQL的锁可以分为全局锁、表级锁、行级锁。

我比较熟悉的是表级锁和行级锁，比如我们对一张表结构进行修改的时候，MySQL就会对这张表加一个元数据锁，元数据锁是属于表级锁的。

行级锁目前只有Innodb存储引擎实现了，MyISAM存储引擎是不支持行级锁的，只有表锁。Innodb存储引擎实现的行级锁主要有记录锁、间隙锁、临键锁、插入意向锁这些，当我们对表记录进行select for update，或者增删改的时候，都会对记录加行级锁。

MySQL 有哪些锁？

全局锁

执行后，整个数据库就处于只读状态了，主要应用于做全库逻辑备份

备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免？

如果数据库的引擎支持的事务支持可重复读的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作

表级锁

MySQL 里面表级别的锁有这几种：

  • 表锁；
  • 元数据锁（MDL）;
  • 意向锁；
  • AUTO-INC 锁；

表锁

对表加共享锁和独占锁

元数据锁

再来说说元数据锁（MDL）。

我们不需要显示的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

  • 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
  • 对一张表做结构变更操作的时候，加的是 MDL 写锁；

意向锁

接着，说说意向锁。

  • 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
  • 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

也就是，当执行插入、更新、删除操作（加行级锁之前），需要先对表加上「意向独占锁」，然后对该记录加独占锁。

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

AUTO-INC 锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

AUTO-INC 锁是特殊的表锁机制，锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

行级锁

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为锁定读。

//对读取的记录加共享锁
select ... lock in share mode;

//对读取的记录加独占锁
select ... for update;

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。

行级锁的类型主要有三类：

  • Record Lock，记录锁，也就是仅仅把一条记录锁上；
  • Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
  • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
  • 插入意向锁

Record Lock

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

Gap Lock

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的。

Next-Key Lock

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

加锁的对象是索引，加锁的基本单位是 next-key lock，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。但是，next-key lock 在一些场景下会退化成记录锁或间隙锁（唯一索引等值查询）

插入意向锁

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

插入意向锁名字虽然有意向锁，但是它并不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

表级锁和⾏级锁有什么区别

表级锁：

  • 锁定整个表，当一个事务获取了对某个表的表级锁时，其他事务就无法同时对该表进行修改操作，直到该事务释放了锁。
  • 表锁；元数据锁（MDL）;意向锁；AUTO-INC 锁；

行级锁：

  • 锁定表中的某一行数据，当一个事务获取了对某一行数据的行级锁时，其他事务仍然可以对表中其他行进行修改操作，只有涉及到被锁定的行才会受到影响。
  • InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。
  • Record Lock，记录锁，也就是仅仅把一条记录锁上；
  • Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
  • Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。
  • 插入意向锁

哪些操作会加表级锁？哪些操作会加⾏级锁？请简单举例说⼀下。

在MySQL中，锁定行为是根据具体的存储引擎（如InnoDB、MyISAM等）和事务隔离级别来确定的。一般来说，在InnoDB存储引擎下，MySQL的锁定行为如下：

表级锁：

  1. DDL语句：像ALTER TABLE、TRUNCATE TABLE、DROP TABLE等会修改表结构的操作会对整个表加表级锁。
  2. 全表查询：如果一个事务执行了类似SELECT * FROM table_name的查询语句，并且在事务未提交前，其他事务无法对这个表进行写操作。

行级锁：

  1. 更新或删除特定行：当执行UPDATE或DELETE语句时，InnoDB会自动给涉及到的行加上行级锁，确保其他事务无法同时修改或删除这些行。
  2. 插入数据：在InnoDB中，插入数据时可能涉及到行级锁，特别是在插入数据时需要满足唯一性约束或者索引约束时，会对相关的行进行锁定，以确保插入操作的原子性。
  3. MySQL的行级锁是在事务级别上的，而不是在语句级别上，所以即使是SELECT语句，如果在事务中使用了合适的锁定方式（如SELECT ... FOR UPDATE），也可以产生行级锁。

当前读和快照读有什么区别

1.当前读 （一致性锁定读）就是给行记录加 X 锁或 S 锁。

当前读的一些常见 SQL 语句类型如下：

# 对读的记录加一个X锁
SELECT...FOR UPDATE
# 对读的记录加一个S锁
SELECT...LOCK IN SHARE MODE
# 对读的记录加一个S锁
SELECT...FOR SHARE
# 对修改的记录加一个X锁
INSERT...
UPDATE...
DELETE...

2、快照读（一致性非锁定读）就是单纯的 SELECT 语句，但不包括下面这两类 SELECT 语句：

SELECT ... FOR UPDATE
# 共享锁 可以在 MySQL 5.7 和 MySQL 8.0 中使用
SELECT ... LOCK IN SHARE MODE;
# 共享锁 可以在 MySQL 8.0 中使用
SELECT ... FOR SHARE;

快照即记录的历史版本，每行记录可能存在多个历史版本（多版本技术）。

快照读的情况下，如果读取的记录正在执行 UPDATE/DELETE 操作，读取操作不会因此去等待记录上 X 锁的释放，而是会去读取行的一个快照。

只有在事务隔离级别 RC(读取已提交) 和 RR（可重读）下，InnoDB 才会使用一致性非锁定读：

  • 在 RC 级别下，对于快照数据，一致性非锁定读总是读取被锁定行的最新一份快照数据。
  • 在 RR 级别下，对于快照数据，一致性非锁定读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。

Next-Key Lock 的加锁范围

Next-Key Lock 将锁定以下范围：

  • 查询条件选中的行（包括满足条件的行和满足条件的范围边界上的行）；
  • 这些行之前的“间隙”，即不存在的键值范围。

举个例子，假设有一个包含整数键值的表，我们执行以下查询语句：

SELECT * FROM table_name WHERE key_column = 10 or key_column = 13 FOR UPDATE;

Next-Key Lock 将锁定：

  • key_column 等于 10 的行，如果存在的话；
  • key_column 等于 13 的行，如果存在的话；
  • 不存在的键值范围，即介于 10 和 13 之间的所有键值范围。

这样做的目的是确保其他事务无法在这个范围内插入新的行，避免了不可重复读和幻读的发生。

MySQL 如何使⽤乐观锁和悲观锁

悲观锁(Pessimistic Lock)是一种并发控制的策略，它假设会发生并发冲突，因此在读取数据之前就先对数据加锁

乐观锁（ Optimistic Locking ） 是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回用户错误的信息，让用户决定如何去做。

悲观锁：

在MySQL中，悲观锁通常使用SELECT ... FOR UPDATE语句实现。当你需要对一组记录进行排他性访问时，可以在SELECT语句上加上FOR UPDATE。

START TRANSACTION;
SELECT * FROM your_table WHERE condition LIMIT 1 FOR UPDATE;
-- 执行你的更新或者插入操作
UPDATE your_table SET column = value WHERE condition;
COMMIT;

乐观锁：

乐观锁通常使用版本号机制实现。你需要在表中添加一个版本号字段，在执行数据的修改操作时，带上一个版本号，一旦版本号和数据的版本号一致就可以执行修改操作并对版本号执行+1操作，否则就执行失败

-- 假设表结构为 `data VARCHAR(255), version INT`
 
-- 查询数据的同时获取版本号
SELECT data, version FROM your_table WHERE condition;
 
-- 更新数据时，同时比较并递增版本号
UPDATE your_table SET data = 'new_value', version = version + 1 WHERE condition AND version = old_version;

在乐观锁的UPDATE语句中，condition是更新的条件，old_version是从SELECT查询中获取的版本号，只有当版本号没有变化时，才会执行更新并递增版本号。

在线上修改表结构，会发生什么

线上环境可能存在很多事务都在读写这张表，如果对这张表进行了表结构修改，就会发生阻塞，原因是有事务对这张表进行读写操作的时候，会生成元数据读锁，而修改表结构的时候，会生成元数据写锁，这时候就产生了读写冲突，所以修改表结构的操作就会阻塞，并且后续事务的增删改操作都会阻塞。

一条Update语句没有带where条件，加的是什么锁

没带where说明没走索引，

  • 可重复读级别下，更新没有带where条件，会全表扫描，会对每一条记录都加next-key锁，相当于锁住了全表。
  • 读已提交隔离级别下，没有间隙锁，更新没有带where条件，是全表扫描，那么会对每一条记录都加记录锁。

带了where条件没有命中索引，加的是什么锁 ：同上

两条更新语句更新同一条记录，加的是什么锁（MySQL怎么加锁）

在可重复读级别下，可能有这些情况：

  • 如果更新条件的字段是唯一索引，还要看更新的记录是否存在：
    • 如果存在，那么这条记录加的记录锁，只锁住该条记录；
    • 如果这条记录不存在，则加间隙锁。
  • 如果更新条件的字段是非唯一索引，还要看更新的记录是否存在：
    • 如果存在，由于非唯一索引会存在相同值的记录，所以非唯一索引等值查询，实际上是一个扫描的过程，那 么会针对符合更新条件的二级索引记录，加next-key锁，最后扫描到第一个不符合更新条件的二级索引记录 就会停止扫描，然后对第一个不符合更新条件的记录加间隙锁（防止幻读），同时，在符合更新条件的记录的主键索引上 加记录锁;
    • 如果不存在，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁.
  • 如果更新条件的字段是没有索引或者没有命中索引，那么就是全表扫描，会对每一条记录都加next-key锁。

第二条更新语句在记录存在的情况下，因为记录被第一条更新语句加了记录锁/next-key锁，所以要等待锁资源释放时，第二条更新语句才能按照相同规则加锁。

当记录不存在时，由于间隙锁可以兼容，所以直接加上间隙锁

可重复读场景，下面的场景会发生什么

  • 事务A和事务B在执行完后update语句后都持有范围为(20，30)的间隙锁

  • 接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、请求和保持、不可剥夺、循环等待，因此发生了死锁。

间隙锁可以共享，间隙锁的意义只在于阻止区间被插入，因此是可以共存的

插入意向锁和间隙锁之间是冲突的

MySQL怎么排查死锁问题

在遇到线上死锁问题时，我们应该第一时间获取相关的死锁日志。我们可以通过show engine innodb status命令来获取死锁信息。

然后就分析死锁日志。

  • 死锁日志通常分为两部分，上半部分说明了事务1在等待什么锁，下半部分说明了事务2当前持有的锁和等待的锁
  • 通过阅读死锁日志，我们可以清楚地知道两个事务形成了怎样的循环等待，然后根据当前各个事务执行的SQL分析出加锁类型以及顺序，逆向推断出如何形成循环等待，这样就能找到死锁产生的原因了

MySQL怎么避免死锁

死锁的四个必要条件：互斥、请求和保持、不可剥夺、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

在数据库层面，有两种策略通过「打破循环等待条件」来解除死锁状态：

  • 设置事务等待锁的超时时间。当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值时 50 秒。

  • 开启主动死锁检测。主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就开启。

上面这个两种策略是「当有死锁发生时」的避免方式

预防死锁：

不能完全预防，有并发，有加锁就会发生死锁，可以降低发生死锁的概率

  • 缩短锁持有时间，减少加锁范围
  • 减少间隙锁（如果幻读和可重复读影响不大，可重复读改成读已提交）

使用社交账号登录

  • Loading...
  • Loading...
  • Loading...
  • Loading...
  • Loading...