锁定查询结果 SELECT FOR UPDATE

OceanBase 数据库支持多版本并发控制（MVCC）。默认情况下，读事务不会阻塞写事务的执行，但可以通过使用 SELECT ... FOR UPDATE 的方式为读事务的对象加锁，从而实现阻塞写事务的目的。

本文通过具体示例介绍如何使用 SELECT ... FOR UPDATE 锁定查询结果。

锁定行为

如果某行数据正在被其他事务锁定，为了避免等待其他事务释放行锁，可以使用 NOWAIT 和 SKIP LOCKED 选项与 SELECT ... FOR UPDATE 带有锁定读的语句一起使用。具体介绍如下：

• 当使用 SELECT ... FOR UPDATE 子句时，有以下行为：

  • 锁等待：如果某行正在被其他事务锁定，则当前事务将等待直到锁被释放或等待超时为止。一旦获得了所需的锁，事务将继续执行。
  • 阻塞其他事务：如果当前事务持有某行的锁，并且另一个事务尝试锁定相同的行，则该事务会被阻塞，直到当前事务释放了行锁。

• 当使用 SELECT ... FOR UPDATE NOWAIT 子句时，有以下行为：

  当一个事务尝试锁定一行时，如果该行已被另一个事务锁定，则会立即返回一个错误，而不是等待锁释放。

• 当使用 SELECT ... FOR UPDATE SKIP LOCKED 子句时，有以下行为：

  当一个事务尝试锁定一行时，如果该行已被另一个事务锁定，则会跳过该行并继续处理下一行。

示例

创建示例表并录入测试数据。

1. 创建表 fruit_order。

   CREATE TABLE fruit_order(
     order_id INT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
     user_id BIGINT NOT NULL COMMENT '客户ID',
     user_name VARCHAR(16) NOT NULL DEFAULT '' COMMENT '客户名称',
     fruit_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '订单金额',
     order_year SMALLINT NOT NULL COMMENT '下单年份',
     PRIMARY KEY (order_id)
     ) COMMENT '订单表';
   

2. 向表 fruit_order 中插入测试数据。

   INSERT INTO fruit_order(user_id, user_name, fruit_price, order_year) VALUES
     (1011,'张三',13.11,'2019'),
     (1011,'张三',22.21,'2020'),
     (1011,'张三',58.83,'2020'),
     (1022,'李四',23.34,'2019'),
     (1022,'李四',12.22,'2019'),
     (1022,'李四',14.66,'2021'),
     (1022,'李四',34.44,'2021'),
     (1033,'王五',51.55,'2020'),
     (1033,'王五',63.66,'2021');
   

使用 FOR UPDATE 锁定查询结果

1. 执行如下语句关闭自动提交功能。

   SET GLOBAL autocommit = 0;
   

   有关自动提交功能的信息，参见 autocommit。

   注意

   设置 Global 级别的变量对当前 Session 无效，需要重新登录建立新的 Session 才会生效。

2. 在会话 1 中执行如下语句，锁定订单 ID 为 7 的查询结果。

   obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE;
   

   返回结果如下：

   +----------+---------+-----------+-------------+------------+
   | order_id | user_id | user_name | fruit_price | order_year |
   +----------+---------+-----------+-------------+------------+
   |        7 |    1022 | 李四      |       34.44 |       2021 |
   +----------+---------+-----------+-------------+------------+
   1 row in set
   

3. 在会话 2 中执行如下语句，修改订单 ID 为 7 的行中 fruit_price 数据为 16.15，该 SQL 语句会等待，直到上面的事务回滚或者 COMMIT 得到执行，否则将等待超时报错为止。

   obclient [test]> UPDATE fruit_order SET fruit_price = 16.15 WHERE order_id = 7;
   

   返回结果如下：

   ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
   

4. 在会话 1 中执行如下语句，提交事务。

   COMMIT;
   

5. 在会话 2 中再次执行如下语句，修改订单 ID 为 7 的行中 fruit_price 数据为 16.15。

   obclient [test]> UPDATE fruit_order SET fruit_price = 16.15 WHERE order_id = 7;
   

   返回结果如下：

   Query OK, 1 row affected
   Rows matched: 1  Changed: 1  Warnings: 0
   

6. 在会话 2 中执行如下语句，提交事务。

   COMMIT;
   

7. 在会话 1 中执行如下语句，查询更新后的数据。

   obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7;
   

   返回结果如下：

   +----------+---------+-----------+-------------+------------+
   | order_id | user_id | user_name | fruit_price | order_year |
   +----------+---------+-----------+-------------+------------+
   |        7 |    1022 | 李四      |       16.15 |       2021 |
   +----------+---------+-----------+-------------+------------+
   1 row in set
   

使用 NOWAIT 或 SKIP LOCKED 选项锁定查询结果

1. 在会话 1 中执行如下语句，使用 FOR UPDATE 锁定订单 ID 为 7 的查询结果。

   obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE;
   

   返回结果如下：

   +----------+---------+-----------+-------------+------------+
   | order_id | user_id | user_name | fruit_price | order_year |
   +----------+---------+-----------+-------------+------------+
   |        7 |    1022 | 李四      |       16.15 |       2021 |
   +----------+---------+-----------+-------------+------------+
   1 row in set
   

2. 在会话 2 中执行如下语句，使用 FOR UPDATE NOWAIT 锁定订单 ID 为 7 的查询结果。

   obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE NOWAIT;
   

   返回结果如下：

   ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
   

3. 在会话 3 中执行如下语句，使用 FOR UPDATE SKIP LOCKED 锁定订单 ID 大于等于 7 的查询结果。

   obclient [test]> SELECT * FROM fruit_order WHERE order_id >= 7 FOR UPDATE SKIP LOCKED;
   

   返回结果如下：

   +----------+---------+-----------+-------------+------------+
   | order_id | user_id | user_name | fruit_price | order_year |
   +----------+---------+-----------+-------------+------------+
   |        8 |    1033 | 王五      |       51.55 |       2020 |
   |        9 |    1033 | 王五      |       63.66 |       2021 |
   +----------+---------+-----------+-------------+------------+
   2 rows in set
   

相关文档

SELECT