This topic provides specific examples on how to use the SELECT ... FOR UPDATE statement to lock query results.
SELECT ... FOR UPDATE
OceanBase Database supports multi-version concurrency control (MVCC). By default, a read transaction does not block a write transaction. You can use the SELECT ... FOR UPDATE statement to lock the objects of the read transaction to block the write transaction.
Examples
Create a table and insert proper data into the table.
obclient> CREATE TABLE fruit_order(
order_id INT NOT NULL AUTO_INCREMENT COMMENT 'Order ID',
user_id BIGINT NOT NULL COMMENT 'Customer ID',
user_name VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'Customer name',
fruit_price DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Order amount',
order_year SMALLINT NOT NULL COMMENT 'Year of orders',
PRIMARY KEY (order_id)
) COMMENT 'Order table';
Query OK, 0 rows affected
obclient>INSERT INTO fruit_order(order_id,user_id,user_name,fruit_price,order_year) VALUES
(1,1011,'Zhang San',13.11,'2019'),
(4,1011,'Zhang San',22.21,'2020'),
(6,1011,'Zhang San',58.83,'2020'),
(2,1022,'Li Si',23.34,'2019'),
(3,1022,'Li Si',12.22,'2019'),
(7,1022,'Li Si',14.66,'2021'),
(8,1022,'Li Si',34.44,'2021'),
(5,1033,'Wang Wu',51.55,'2020'),
(9,1033,'Wang Wu',63.66,'2021');
Query OK, 9 rows affected
Records: 9 Duplicates: 0 Warnings: 0
Use the SELECT ... FOR UPDATE statement to lock query results
obclient> SELECT user_name, fruit_price, order_year FROM fruit_order WHERE order_id = 7 FOR UPDATE;
+-----------+-------------+------------+
| user_name | fruit_price | order_year |
+-----------+-------------+------------+
| Li Si | 16.52 | 2021 |
+-----------+-------------+------------+
1 row in set
/*Execute the following statement in Session 2. This SQL statement will not be executed until the preceding transaction is rolled back or committed.*/
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
/*Execute the following statement in Session 1:*/
obclient> commit;
Query OK, 0 rows affected
/*Execute the following statement again in Session 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
/*Query the updated data in Session 1.*/
obclient> SELECT user_name, fruit_price, order_year FROM fruit_order WHERE order_id = 7 FOR UPDATE;
+-----------+-------------+------------+
| user_name | fruit_price | order_year |
+-----------+-------------+------------+
| Li Si | 16.5 | 2021 |
+-----------+-------------+------------+
1 row in set
