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 provides specific examples on how to use the SELECT ... FOR UPDATE statement to lock query results.
Lock actions
If a row is locked by another transaction, you can use the NOWAIT and SKIP LOCKED options in the SELECT ... FOR UPDATE statement that locks reads without waiting for the transaction to release the row lock. Take note of the following considerations:
The
SELECT ... FOR UPDATEclause performs the following actions:- Lock wait: If a row is locked by another transaction, the current transaction waits until the lock is released or until a timeout expires. After the required lock is obtained, the transaction execution resumes.
- Block other transactions: If the current transaction holds the lock of a row, another transaction that attempts to lock the same row will be blocked until the current transaction releases the row lock.
The
SELECT ... FOR UPDATE NOWAITclause performs the following action:When the current transaction attempts to lock a row, if the row has been locked by another transaction, the current transaction immediately returns an error instead of waiting for the lock to be released.
The
SELECT ... FOR UPDATE SKIP LOCKEDclause performs the following action:When the current transaction attempts to lock a row, if the row has been locked by another transaction, the current transaction skips the row and proceeds to the next row.
Examples
Create a table and insert test data into the table.
Create a table named
fruit_order.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';Insert test data into the
fruit_ordertable.INSERT INTO fruit_order(user_id, user_name, fruit_price, order_year) VALUES (1011,'Zhang San',13.11,'2019'), (1011,'Zhang San',22.21,'2020'), (1011,'Zhang San',58.83,'2020'), (1022,'Li Si',23.34,'2019'), (1022,'Li Si',12.22,'2019'), (1022,'Li Si',14.66,'2021'), (1022,'Li Si',34.44,'2021'), (1033,'Wang Wu',51.55,'2020'), (1033,'Wang Wu',63.66,'2021');
Use the FOR UPDATE option to lock query results
Disable the autocommit feature.
SET GLOBAL autocommit = 0;For more information about the autocommit feature, see autocommit.
Notice
A global variable does not take effect for the current session but for new sessions established upon re-login.
Lock the row where the
order_idvalue is7in session 1.obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE;The return result is as follows:
+----------+---------+-----------+-------------+------------+ | order_id | user_id | user_name | fruit_price | order_year | +----------+---------+-----------+-------------+------------+ | 7 | 1022 | Li Si | 34.44 | 2021 | +----------+---------+-----------+-------------+------------+ 1 row in setChange the
fruit_pricevalue to16.15for the row where theorder_idvalue is7in session 2. This transaction remains pending until the preceding transaction is rolled back or committed. If it is not executed before timing out, a timeout error is returned.obclient [test]> UPDATE fruit_order SET fruit_price = 16.15 WHERE order_id = 7;The return result is as follows:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionCommit the transaction in session 1.
COMMIT;Change the
fruit_pricevalue to16.15for the row where theorder_idvalue is7again in session 2.obclient [test]> UPDATE fruit_order SET fruit_price = 16.15 WHERE order_id = 7;The return result is as follows:
Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0Commit the transaction in session 2.
COMMIT;Query the updated data in session 1.
obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7;The return result is as follows:
+----------+---------+-----------+-------------+------------+ | order_id | user_id | user_name | fruit_price | order_year | +----------+---------+-----------+-------------+------------+ | 7 | 1022 | Li Si | 16.15 | 2021 | +----------+---------+-----------+-------------+------------+ 1 row in set
Use the NOWAIT or SKIP LOCKED option to lock query results
Use the
FOR UPDATEoption to lock the row where theorder_idvalue is7in session 1.obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE;The return result is as follows:
+----------+---------+-----------+-------------+------------+ | order_id | user_id | user_name | fruit_price | order_year | +----------+---------+-----------+-------------+------------+ | 7 | 1022 | Li Si | 16.15 | 2021 | +----------+---------+-----------+-------------+------------+ 1 row in setUse the
FOR UPDATE NOWAIToption to lock the row where theorder_idvalue is7in session 2.obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE NOWAIT;The return result is as follows:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionUse the
FOR UPDATE SKIP LOCKEDoption to lock the row where theorder_idvalue is greater than or equal to7in session 3.obclient [test]> SELECT * FROM fruit_order WHERE order_id >= 7 FOR UPDATE SKIP LOCKED;The return result is as follows:
+----------+---------+-----------+-------------+------------+ | order_id | user_id | user_name | fruit_price | order_year | +----------+---------+-----------+-------------+------------+ | 8 | 1033 | Wang Wu | 51.55 | 2020 | | 9 | 1033 | Wang Wu | 63.66 | 2021 | +----------+---------+-----------+-------------+------------+ 2 rows in set