前言

  • 在数据库事务处理中,处理数据库并发访问的请求一个复杂且重要的问题,当多个事务同时访问并尝试修改同一行数据时,可能会导致数据的一致性问题,例如丢失更新或脏读的情况。为了解决这些问题,数据库提供了一些机处理并发事务,其中之一便是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加锁的情况:
    • 主键字段:加行锁。
    • 唯一索引字段:加行锁。
    • 普通索引字段:加行锁。
    • 普通字段:加表锁。
  • 如果事务一加了行锁,一直没有释放锁,事务二操作相同行的数据时,会一直等待直到超时。
  • 如果事务一加了表锁,一直没有释放锁,事务二不管操作的是哪一行数据,都会一直等待直到超时。