前言

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