当前位置：首页 > article >正文

MySQL行锁的实践

article 2024/11/13 10:10:30

在MySQL中，根据加锁的粒度，可以将数据库的锁细分为表锁、行锁、页锁。其中，表锁(Table Lock)是一种粗粒度的锁，它锁定整个表，阻止其他事务访问表中的任何行；行锁(Row Lock)是一种细粒度的锁，它锁定指定的行；页锁(Page Lock)是介于行锁和表锁之间的一种锁机制，它锁定表的一个页或多个页。此外，无论是表锁，还是行锁，都可以根据是否独占数据还是共享数据，进一步细分为共享锁和排他锁。这里重点介绍下行锁，关于表锁和页锁可以参考笔者之前的MySQL锁概述一文。

行锁的实现方式

对不同的存储引擎，锁的支持情况和使用方式是不同的。如MyISAM只支持表级锁，不支持行锁。InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。需要说明的是，即便在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引。如果MySQL认为全表扫描效率更高，即使指定了索引字段，也不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，有必要进一步检查SQL的执行计划，以确认是否真正使用了索引。
InnoDB为实现行锁，提供了多种实现方式。如记录锁(Record Lock)、间隙锁(Gap Lock)、临键锁(Next-Key Lock)等。需要说明的是，行锁仅在使用索引字段时才会生效，否则会升级为表锁。

记录锁(Record Lock)

记录锁是封锁记录，记录锁也叫行锁，示例如下：

SELECT * FROM `test_table` WHERE `id` = 1 FOR UPDATE;

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

间隙锁(Gap Lock)

间隙锁，锁定一个范围，但不包含记录本身，间隙锁是封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。间隙锁主要目的，也是为了避免出现幻读(Phantom Read)，对应的事务的隔离级别降级为可重复读。如果将事务的隔离性级别调整成读已提交或读未提交，间隙锁都会失效。间隙锁对应的事务隔离级别是可重复。示例如下：

SELECT * FROM `test_table` WHERE `id` > 100 FOR UPDATE

以下情况均会产生间隙锁：
(1) 使用普通索引，且需要锁定一个区间。
(2) 使用多列唯一索引，且需要锁定一个区间。
(3) 使用唯一索引，且需要锁定一个区间。
注意，间隙锁封锁的不是多条记录，而是一个区间，以防止出现幻读现象。

临键锁(Next-Key Lock)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。同间隙锁一样，临键锁也是为了避免出现幻读(Phantom Read)。同样的，临键锁对应的事务隔离级别是可重复。示例如下：

SELECT * FROM table WHERE age >=24 FOR UPDATE;

临键锁会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中临键锁只与非唯一索引列有关，在唯一索引列(包括主键列)上不存在临键锁。

行锁的共享锁或排他锁

行锁支持设置共享模式或排他模式。其中共享模式允许其他事务读操作，不允许其他事务写操作，注意共享模式也不支持当前事务写操作。排他模式不允许其他事务读操作。不允许其他事务写操作。

共享

共享行锁的设置如下：

SELECT ... FOR SHARE

多事务并发环境中，通过LOCK IN SHARE MODE可以避免脏读（Dirty Read），即读取到其他事务未提交的数据。
SELECT … FOR SHARE 是 SELECT … LOCK IN SHARE MODE的升级写法，不同的用户应该根据当前的MySQL版本选择合适的写法。注意，MySQL兼容支持LOCK IN SHARE MODE的写法。此外，FOR SHARE支持NOWAIT和, SKIP LOCKED等选项。

排他

排他行锁的设置如下：

SELECT ... FOR UPDATE

共享锁适用于那些需要确保读取的数据在事务期间不被修改，但允许其他事务并发读取的场景，排他锁则适用于需要修改数据的场景，确保在修改过程中数据不会被其他事务读取或修改。

行锁的释放

无论是SHARE模式，还是UPDATE模式的行锁，都是在事务提交(commit)或事务回滚(rollback)的时候释放行锁。

行锁的等待

默认情况下，对于没有获得行锁的事务需要一直等待行锁的释放，这样可能会影响其他事务的执行。一种有效的处理方式是设置锁的等待策略。如指定等待时间、不等待、跳过锁定的数据等。MySQL不支持等待指定的时间(国产数据库OceanBase则支持设置行锁的等待时间，示例如下：SELECT … FOR UPDATE WAIT seconds)，但支持设置不等待、跳过锁定的数据。示例语句如下：

SELECT ... FOR UPDATE NOWAIT
SELECT ... FOR UPDATE SKIP LOCKED

注意，以上能力是在MySQL 8.4版本及之后的版本才引入的功能。在使用的时候，要注意当前使用的MySQL版本。如果是低版本的MySQL，可以考虑在应用层做一些简单地处理。

行锁的超时时间

在MySQL中，不支持针对每个行锁实例设置超时时间，但是支持对所有的行锁设置超时时间，对应的参数是innodb_lock_wait_timeout。这个参数决定了InnoDB在放弃获取锁之前等待的时间长度（以秒为单位）。如果在指定的时间内无法获取锁，需要获取锁的事务将被回滚，并返回一个错误。innodb_lock_wait_timeout 的默认值取决于 MySQL 版本。通常，默认值是 50 秒。行锁的超时设置示例如下：

// 全局设置
SET GLOBAL innodb_lock_wait_timeout = 30; -- 设置全局的超时时间为 30 秒
// Session设置
SET SESSION innodb_lock_wait_timeout = 30; -- 设置当前会话的超时时间为 30 秒

这样的话，如果在指定时间范围内无法获取锁，获取锁的事务将返回一个错误，提示锁等待超时。

行锁使用总结

行锁的使用场景，主要是用于需要实现细粒度锁控制的场景，如高并发场景。InnoDB为提供了多种行锁的实现方式，如记录锁(Record Lock)、间隙锁(Gap Lock)、临键锁(Next-Key Lock)等。需要说明的是，行锁仅在使用索引字段时才会生效，否则会升级为表锁。
在使用行锁时，支持设置共享模式或排他模式。其中共享模式允许其他事务读操作，不允许其他事务写操作，注意共享模式也不支持当前事务写操作。排他模式不允许其他事务读操作，不允许其他事务写操作。
行锁的释放，都是在事务提交(commit)或事务回滚(rollback)的时候。
默认情况下，对于没有获得行锁的事务需要一直等待行锁的释放，这样可能会影响其他事务的执行。MySQL支持设置不等待(NOWAIT)、跳过锁定的数据(SKIP LOCKED)等选项来处理锁等待的情况。注意，上述设置仅在MySQL 8.4及更高版本才支持。
此外，可以为行锁设置超时时间。注意，MySQL不支持针对每个行锁实例设置超时时间，仅支持对所有的行锁设置超时时间，对应的参数是innodb_lock_wait_timeout。innodb_lock_wait_timeout 的默认值取决于 MySQL 版本。通常，默认值是 50 秒。这样的话，如果在指定时间范围内无法获取锁，获取锁的事务将返回一个错误，提示锁等待超时。

参考

https://blog.csdn.net/winy_lm/article/details/48175885 oracle for update和for update nowait的区别
https://docs.pingcode.com/baike/2033002 mysql数据库如何加行锁
https://dev.mysql.com/doc/refman/8.4/en/innodb-locking-reads.html Locking Reads
https://yiyan.baidu.com 文心一言

查看全文

http://www.kler.cn/a/310787.html