前言

  • 在数据库事务处理中，处理数据库并发访问的请求一个复杂且重要的问题，当多个事务同时访问并尝试修改同一行数据时，可能会导致数据的一致性问题，例如丢失更新或脏读的情况。为了解决这些问题，数据库提供了一些机处理并发事务，其中之一便是SELECT ... FOR UPDATE语句。
  • 在并发访问的环境中，SELECT ... FOR UPDATE允许事务在选择数据的同时，锁定这些数据，防止其他事务修改这些数据，直到当前事务完成并释放锁。从本质上，SELECT ... FOR UPDATE是一种悲锁的用法，一般情况下，只会锁住一行数据，但是如果没有正确使用的话，会把整张表都锁住的。
  • 我也在实际项目中使用过，例如免费字数抵扣金额下单的场景。

实践

  • 虽然在MySQL中是通过SELECT ... FOR UPDATE语句来实现的行锁的功能。但是如果你在实际工作中使用不正确，也容易把整张表锁住，严重影响性能。SELECT ... FOR UPDATE语句的用法是否正确，跟WHERE条件中的参数有很大的关系。我们先来简单建个表，然后分析一下下面几种情况。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE user_info (
    id INT PRIMARY KEY,             -- 主键
    username VARCHAR(255) UNIQUE,   -- 唯一索引
    email VARCHAR(255) NOT NULL,    -- 普通索引
    amount DECIMAL(10, 2),          -- 普通字段
    INDEX idx_email (email)         
);


INSERT INTO user_info (id, username, email, amount)
VALUES
  (1, 'john_doe', 'john.doe@example.com', 500.00),
  (2, 'alice_smith', 'alice.smith@example.com', 1000.00),
  (3, 'bob_jones', 'bob.jones@example.com', 1500.00),
  (4, 'lisa_davis', 'lisa.davis@example.com', 2000.00),
  (5, 'charlie_brown', 'charlie.brown@example.com', 2500.00);

主键字段

  • 当WHERE条件使用主键时
  • 在事务一中使用FOR UPDATE，加一个行锁，注意此时没有COMMIT提交事务
1
2
START TRANSACTION;  -- 开启事务
SELECT * FROM user_info WHERE id = 1 FOR UPDATE;  -- 加行锁
  • 在事务二中尝试修改该行，那么在执行的过程中，会一直等待事务一释放锁。
1
UPDATE user_info SET amount = 10000 WHERE id = 1;
  • 如果事务一，一直都不释放锁，那么事务二最终会报这个异常
1
2
3
UPDATE user_info SET amount = 10000 WHERE id = 1
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 查询时间: 50.086s
  • 当事务一提交事务后，事务二才能正常执行
1
COMMIT;
  • 如果此时开启一个事务三，修改其他行的值，那么是可以正常执行的
1
UPDATE user_info SET amount = 10000 WHERE id = 2;
  • 执行结果
1
2
3
UPDATE user_info SET amount = 10000 WHERE id = 2
> Affected rows: 1
> 查询时间: 0.009s

唯一索引

  • 当WHERE条件使用唯一索引时
  • 在事务一中使用FOR UPDATE，加一个行锁，注意此时没有COMMIT提交事务
1
2
START TRANSACTION;
SELECT * FROM user_info WHERE username = 'john_doe' FOR UPDATE;
  • 在事务二中尝试修改该行，那么在执行的过程中，会一直等待事务一释放锁。
1
UPDATE user_info SET amount = 10000 WHERE username = 'john_doe';
  • 如果事务一，一直都不释放锁，那么事务二最终会报这个异常
1
2
3
UPDATE user_info SET amount = 10000 WHERE username = 'john_doe'
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 查询时间: 50.084s
  • 当事务一提交事务后，事务二才能正常执行
1
COMMIT;
  • 如果此时开启一个事务三，修改其他行的值，那么是可以正常执行的
1
UPDATE user_info SET amount = 1000 WHERE username = 'alice_smith';
  • 执行结果
1
2
3
UPDATE user_info SET amount = 1000 WHERE username = 'alice_smith'
> Affected rows: 1
> 查询时间: 0.002s

普通索引

  • 当WHERE条件使用普通索引时
  • 在事务一中使用FOR UPDATE，加一个行锁，注意此时没有COMMIT提交事务
1
2
START TRANSACTION;
SELECT * FROM user_info WHERE email = 'john.doe@example.com' FOR UPDATE;
  • 在事务二中尝试修改该行，那么在执行的过程中，会一直等待事务一释放锁。
1
UPDATE user_info SET amount = 1000 WHERE email = 'john.doe@example.com';
  • 如果事务一，一直都不释放锁，那么事务二最终会报这个异常
1
2
3
UPDATE user_info SET amount = 1000 WHERE email = 'john.doe@example.com'
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 查询时间: 50.082s
  • 当事务一提交事务后，事务二才能正常执行
1
COMMIT;
  • 如果此时开启一个事务三，修改其他行的值，那么是可以正常执行的
1
UPDATE user_info SET amount = 10000 WHERE email = 'alice.smith@example.com';
  • 执行结果
1
2
3
UPDATE user_info SET amount = 10000 WHERE email = 'alice.smith@example.com'
> Affected rows: 1
> 查询时间: 0.003s

普通字段

  • 当WHERE条件使用普通字段时
  • 在事务一中使用FOR UPDATE，加一个行锁，注意此时没有COMMIT提交事务
1
2
START TRANSACTION;
SELECT * FROM user_info WHERE amount = 2500 FOR UPDATE;
  • 在事务二中尝试修改该行，那么在执行的过程中，会一直等待事务一释放锁。
1
UPDATE user_info SET amount = 1000 WHERE amount = 2500;
  • 如果事务一，一直都不释放锁，那么事务二最终会报这个异常
1
2
3
UPDATE user_info SET amount = 1000 WHERE amount = 2500
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 查询时间: 50.17s
  • 当事务一提交事务后，事务二才能正常执行
1
COMMIT;
  • 如果此时开启一个事务三，修改其他行的值，会发现也是被阻塞了的
1
UPDATE user_info SET amount = 1000 WHERE amount = 2000;
  • 执行结果
1
2
3
UPDATE user_info SET amount = 1000 WHERE amount = 2000
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 查询时间: 50.083s

结论

  • 总结一下SELECT ... FOR UPDATE加锁的情况：
    • 主键字段：加行锁。
    • 唯一索引字段：加行锁。
    • 普通索引字段：加行锁。
    • 普通字段：加表锁。
  • 如果事务一加了行锁，一直没有释放锁，事务二操作相同行的数据时，会一直等待直到超时。
  • 如果事务一加了表锁，一直没有释放锁，事务二不管操作的是哪一行数据，都会一直等待直到超时。