OceanBase Database supports multiversion 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.
This topic uses examples to describe how to use the SELECT FOR UPDATE statement to lock query results.
Example: Use the SELECT FOR UPDATE statement to lock query results
/*Execute the following statement in session 1:*/
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
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