跳至主要內容

MySQL 的 For Update 行级锁

Mr.Liu2024年7月5日大约 12 分钟数据库MySQL

一、MySQL 的 For Update 简介

For update 是 MySQL 中用于实现行锁的一种语法，其主要作用是在 SELECT 查询语句中加上 FOR UPDATE 子句，以保证查询结果集中的每一行都被锁定，避免其他事务对这些行进行修改。具体语法如下：

SELECT ... FROM table_name WHERE ... FOR UPDATE;

在执行 For update 语句时，MySQL 会首先获取表级共享锁，然后再根据 WHERE 条件锁定符合条件的行。锁定的范围包括了 WHERE 条件筛选出来的所有行，即使有些行并不满足 WHERE 条件。

需要注意的是，For update 语句会将锁定的行一直锁定到事务结束，因此在使用时需要考虑是否会导致死锁的问题，并尽量缩小锁定的范围。同时，对于 InnoDB 存储引擎的表，可以使用行级锁和间隙锁来提高并发性能。

1.1、For Update 的作用

"For Update" 是一种 SQL 语句，它的作用是在查询过程中锁定数据行，防止其他事务对这些数据进行修改操作。使用 "For Update" 可以保证当前事务对被锁定的数据具有排他性，并且能够避免产生并发问题。"For Update" 适用于需要修改或者删除数据的场景，例如在一个订单系统中，如果多个用户同时尝试对同一个订单进行修改操作，就可以使用 "For Update" 方式来避免冲突。

1.2、For Update 与其他锁定方式的区别

  1. For Update 是一种排它锁定方式，只有被锁定的行才能进行修改操作。而共享锁定方式允许多个事务同时读取同一行数据，但是不允许对其进行修改。
  2. For Update 锁定方式可以避免出现脏读、不可重复读和幻读等并发问题，因为它会在读取某一行数据时将其锁定，直到事务结束或提交之前才释放锁定。
  3. For Update 锁定方式可能会导致死锁问题，因为多个事务可能会互相等待对方释放锁定。而其他锁定方式如共享锁定则不存在这个问题。
  4. For Update 锁定方式的性能较差，因为它需要在读取数据时进行锁定操作，增加了系统的开销。而其他锁定方式如共享锁定则没有这个问题。

二、For Update 的语法

2.1、SELECT 语句的基本语法

SELECT column1, column2, ... FROM table_name WHERE condition;

其中，column1、column2 等为要查询的列名，用逗号分隔；table_name 为要查询的表名；condition 为查询条件，可选。

2.2、mysql 如何开启事务和提交事务？

MySQL 中使用 START TRANSACTION 命令来开启一个事务，使用 COMMIT 命令来提交事务。开启事务后，在数据库中所做的所有更改都不会立即写入磁盘，而是被缓存到内存中。只有在事务提交时，这些更改才会被保存到磁盘上。

下面是一个简单的示例，演示如何开启并提交一个事务：

-- 开启事务
START TRANSACTION;

-- 执行一系列SQL语句，对数据进行修改
INSERT INTO my_table (col1, col2) VALUES ('value1', 'value2');
UPDATE my_table SET col3 = 'new_value' WHERE col1 = 'value1';

-- 提交事务
COMMIT;

在上述示例中，使用 START TRANSACTION 命令开启了一个事务，并在之后执行了一系列对数据进行修改的 SQL 语句。最后使用 COMMIT 命令提交了事务，从而将所有的修改操作保存到了磁盘上。

如果需要取消事务所做的修改操作，可以使用 ROLLBACK 命令来回滚事务。例如：

-- 开启事务
START TRANSACTION;

-- 执行一系列SQL语句，对数据进行修改
INSERT INTO my_table (col1, col2) VALUES ('value1', 'value2');
UPDATE my_table SET col3 = 'new_value' WHERE col1 = 'value1';

-- 回滚事务
ROLLBACK;

在上述示例中，使用 ROLLBACK 命令回滚了事务，从而取消了所有已经执行的修改操作。

2.3、使用 For Update 进行数据锁定

使用 For Update 需要注意以下几点：

  1. For Update 只能应用于 SELECT 语句中。
  2. 对于使用了 For Update 的 SELECT 语句，只有当事务提交或回滚时，所加的锁才会被释放。
  3. 在使用 For Update 时，要确保事务的隔离级别为 Serializable 级别，因为只有这个级别能够保证数据的完整性和一致性。

下面是一个使用 For Update 进行数据锁定的示例：

START TRANSACTION;

SELECT * FROM table_name WHERE id = 1 FOR UPDATE;

UPDATE table_name SET column_name = 'new_value' WHERE id = 1;

COMMIT;

先使用 SELECT 语句来选取待更新的行，并在其后添加 FOR UPDATE 关键字，从而将该行数据进行锁定。然后再执行 UPDATE 语句来更新该行数据。最后通过 COMMIT 确认事务完成，从而释放锁定。

三、如何使用 For Update

3.1、For Update 的应用场景

  1. 数据库更新：当数据库中某些记录需要进行修改、删除或插入时，可以使用 For Update 锁定这些记录，以确保数据操作的正确性和一致性。
  2. 并发控制：在并发访问情况下，多个线程可能会同时读取和写入同一份数据，使用 For Update 可以避免并发问题，保证数据的完整性和准确性。
  3. 事务处理：在事务中，For Update 可以确保对共享资源的互斥访问，防止出现数据不一致的情况。
  4. 缓存更新：使用 For Update 可以在缓存中更新数据，保证在多个进程或线程中操作缓存时的数据一致性。
  5. 悲观锁控制：For Update 是一种悲观锁控制方法，可以确保在对共享资源进行操作前，先将其锁定，以防止其他线程对其修改。

3.2、使用案例分析

（1）并发读取和更新操作。

假设有一个银行账户表，其中包含账户余额。为了保证并发操作在更新账户余额时不会出现问题，可以使用 FOR UPDATE 语句锁定数据行。

例如，以下 SQL 查询会对指定的账户进行锁定：

SELECT balance FROM accounts WHERE account_number = '12345' FOR UPDATE;

这将锁定具有帐户号码 “12345” 的帐户行，并且在事务提交之前，其他事务将无法修改该行。接下来，可以更新余额，例如：

UPDATE accounts SET balance = balance - 100 WHERE account_number = '12345';

在此示例中，“12345” 账户的余额将被减少 100 元。在提交事务之后，其他事务才有机会访问该行并进行更新。

（2）多个事务同时访问同一条记录。

使用 For Update 语句可以在多个事务同时访问同一条记录时实现加锁，保证数据的一致性。

在 MySQL 中，For Update 语句可以在 SELECT 语句中使用，如：

SELECT * FROM table_name WHERE id=1 FOR UPDATE;

这条语句会锁定 id 为 1 的记录，其他事务需要等待该事务释放锁才能对该记录进行操作。

当有多个事务同时访问同一条记录时，只有一个事务能够获得锁，其他事务需要等待。如果获得锁的事务没有及时释放锁，则其他事务可能会发生死锁现象。

需要注意的是，在使用 For Update 语句时应尽量控制事务的范围，避免锁定过多的记录，影响数据库的并发性能。

（3）防止脏读取和不可重复读取。

假设有一个银行账户表，其中包含账户名、余额和最近更新时间这三个字段。现在需要对某个账户进行转账操作，并且要求转账过程中不能出现脏读取和不可重复读取的情况。

使用 For Update 可以实现这一要求。For Update 是 MySQL 提供的一种行级锁定机制，它可以锁定查询结果集中的指定行，从而避免其他事务对这些行进行修改或删除操作。

下面是一个示例代码，假设要将账户 A 中的 100 元转账给账户 B：

START TRANSACTION;

SELECT balance FROM accounts WHERE account_name = 'A' FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE account_name = 'A';
UPDATE accounts SET balance = balance + 100 WHERE account_name = 'B';

COMMIT;

在上述代码中，首先使用 SELECT 语句查询账户 A 的余额，并使用 FOR UPDATE 子句对其进行锁定。这意味着在当前事务未提交之前，其他事务无法对账户 A 的余额进行修改或删除操作，从而避免了脏读取和不可重复读取的情况。接下来，使用两个 UPDATE 语句分别将账户 A 的余额减少 100 元，将账户 B 的余额增加 100 元，并在最后进行事务提交。

需要注意的是，使用 For Update 会对性能产生一定的影响，因此应该谨慎使用。在实际应用中，可以根据具体情况进行优化和调整。

四、For Update 的注意事项

4.1、For Update 的局限性

For Update 虽然可以解决部分并发问题，但也存在一定的局限性。

  1. For Update 只能用于 InnoDB 引擎。如果使用 MyISAM 等其他引擎，无法使用 For Update。
  2. For Update 会对查询结果集中的行进行锁定，这意味着其他事务无法对这些行进行修改或删除操作。如果锁定时间过长，可能会导致其他事务出现阻塞，从而影响系统的并发性能。
  3. 如果在一个事务中多次使用 For Update 语句锁定同一行，则后面的 For Update 语句可能会被前面的 For Update 语句所覆盖。因此，在使用 For Update 时应该谨慎考虑锁定的粒度，尽量避免对同一行进行多次锁定操作。
  4. 在 MySQL 8.0 版本之前，For Update 只能锁定整个表、整个分区或整个索引。如果要锁定某个特定的行，需要使用 WHERE 子句来指定条件。但是，这种方式会增加锁定的粒度，可能会导致更多的阻塞和性能问题。

4.2、不当使用 For Update 可能导致的问题

  1. 死锁：如果多个事务同时使用 For Update 锁定相同的资源，可能会导致死锁。
  2. 性能问题：使用 For Update 会锁定相应的行，这可能会影响并发性和性能。
  3. 数据一致性问题：在事务中使用 For Update 锁定行时，如果在锁定期间其他事务对相同的数据进行了更新，则当前事务可能会读取到已过时的数据。
  4. 阻塞问题：如果一个事务长时间持有 For Update 锁，其他事务将无法访问相应的资源，这可能会导致阻塞问题。
  5. 安全问题：当使用 For Update 时，必须小心不要将代码中的所有查询都添加上锁，否则可能会导致安全问题。例如，如果一个查询返回敏感信息，但未加锁，则可能会导致信息泄漏。

4.3、For Update 一定只锁行吗？

MySQL 的 FOR UPDATE 语句只会对查询结果集中的行进行行锁，其他事务无法修改被锁定的行。但需要注意的是，在多表查询时，如果没有正确地使用 JOIN 操作或者没有设置正确的索引，可能会导致表级锁定而不是行级锁定。

比如有两个表 A 和 B，A 表中有 id 和 name 字段，B 表中有 id 和 age 字段。现在要查询 A 表中所有名字为 “Tom” 的记录，并更新对应的 B 表中的年龄。

如果使用如下的 SQL 语句：

SELECT * FROM A WHERE name = 'Tom' FOR UPDATE;
UPDATE B SET age = 30 WHERE id = ?;

如果 A 表中的 name 字段没有索引，那么 MySQL 就会对整个 A 表进行表级锁定，而不是对查询结果集中的行进行行级锁定。这样，即使只有一条记录满足条件，也会导致其他事务无法访问 A 表，从而影响系统的并发性能。因此，在使用 FOR UPDATE 时，需要注意表的索引情况，尽量避免表级锁定的出现。

五、总结

5.1、理解 For Update 的优缺点

For Update 是一种 SQL 语句，用于锁定指定的行或表，以保证在事务中其他用户不能修改这些数据。

优点：

  1. 数据的完整性：使用 For Update 可以避免并发操作导致数据不一致的问题。
  2. 提高效率：通过锁定数据，可以减少多个事务同时对同一行数据进行修改的情况，从而提高了查询和更新的效率。
  3. 简化代码：通过使用 For Update，开发人员可以简化代码，并减少手动处理并发操作带来的风险。

缺点：

  1. 并发性降低：使用 For Update 会阻塞其他事务对锁定的数据进行修改，因此可能会导致并发性降低。
  2. 死锁风险：如果多个事务之间互相等待对方释放锁，则可能会出现死锁的情况。
  3. 内存开销：For Update 需要占用一定的内存资源，当并发量很大时，可能会占用更多的内存资源，从而影响系统的稳定性。

5.2、合理使用 For Update，提升数据库性能

For Update 是一种数据库锁定机制，用来保护数据的一致性和完整性。在进行修改操作时，For Update 会锁定相应记录，防止其他事务同时修改同一条数据造成数据不一致。

合理使用 For Update 可以提升数据库性能，主要表现在以下几个方面：

  1. 减少数据库死锁情况：使用 For Update 可以有效地避免多个事务同时对同一条数据进行修改而造成的死锁问题。
  2. 提高并发处理能力：For Update 只会锁定相应的记录，而不是整个表，因此可以在保证数据一致性的前提下支持更多的并发操作。
  3. 缩短事务执行时间：使用 For Update 可以减少事务的回滚次数，缩短事务的执行时间，从而提升数据库的性能。

但是，过度使用 For Update 也会带来一些副作用，如增加数据库的锁定和等待时间、降低数据库的并发处理能力等。因此，在使用 For Update 时需要根据具体情况进行合理的优化和调整。

备注

原文地址 https://zhuanlan.zhihu.com/p/626926704