OceanBase Cloud快速入门第76期：如何锁定查询结果 SELECT FOR UPDATE?

欢迎访问 OceanBase 官网获取更多信息：https://www.oceanbase.com/

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

SELECT FOR UPDATE

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

示例

创建示例表并录入合适的数据。

obclient> 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 '订单表';
Query OK, 0 rows affected

obclient>INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
  (1,1011,'张三',13.11,'2019'),
  (4,1011,'张三',22.21,'2020'),
  (6,1011,'张三',58.83,'2020'),
  (2,1022,'李四',23.34,'2019'),
  (3,1022,'李四',12.22,'2019'),
  (7,1022,'李四',14.66,'2021'),
  (8,1022,'李四',34.44,'2021'),
  (5,1033,'王五',51.55,'2020'),
  (9,1033,'王五',63.66,'2021');
Query OK, 9 rows affected
Records: 9  Duplicates: 0  Warnings: 0
复制

使用 SELECT FOR UPDATE 锁定查询结果

obclient> SELECT user_name, fruit_price, order_year FROM fruit_order WHERE order_id = 7 FOR UPDATE;
+-----------+-------------+------------+
| user_name | fruit_price | order_year |
+-----------+-------------+------------+
| 李四      |       16.52 |       2021 |
+-----------+-------------+------------+
1 row in set

/*在会话 2 中执行如下语句，该 SQL 语句会等待,直到上面的事务回滚或者 COMMIT 才得到执行*/ 
obclient> SELECT user_name, fruit_price, order_year FROM fruit_order WHERE order_id = 7 FOR UPDATE;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

/*在会话 1 中执行如下语句：*/
obclient> commit;
Query OK, 0 rows affected

/*在会话 2 中再次执行如下语句：*/
obclient> UPDATE fruit_order SET fruit_price = 16.5  WHERE order_id = 7;
Query OK, 0 rows affected
Rows matched: 1  Changed: 0  Warnings: 0

/*在会话 1 中查询更新后的数据*/
obclient> SELECT user_name, fruit_price, order_year FROM fruit_order WHERE order_id = 7 FOR UPDATE;
+-----------+-------------+------------+
| user_name | fruit_price | order_year |
+-----------+-------------+------------+
| 李四      |       16.5 |       2021 |
+-----------+-------------+------------+
1 row in set
复制

欢迎访问 OceanBase 官网获取更多信息：https://www.oceanbase.com/