Use the SELECT FOR UPDATE statement to lock query results

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 order placement',
  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