OceanBase AI Database supports multi-version concurrency control (MVCC). By default, read transactions do not block write transactions. However, you can use the SELECT ... FOR UPDATE statement to lock the objects being read by a transaction, thereby blocking write transactions.
This topic provides a detailed example of how to use SELECT ... FOR UPDATE to lock query results.
Lock behavior
If a row is already locked by another transaction, you can use the NOWAIT or SKIP LOCKED option with the SELECT ... FOR UPDATE statement to avoid waiting for the lock to be released. The specific behavior is as follows:
When using the
SELECT ... FOR UPDATEclause:- Lock wait: If a row is locked by another transaction, the current transaction waits until the lock is released or the wait times out. Once the required lock is acquired, the transaction continues.
- Block other transactions: If the current transaction holds a lock on a row and another transaction tries to lock the same row, that transaction is blocked until the current transaction releases the row lock.
When using the
SELECT ... FOR UPDATE NOWAITclause:When a transaction attempts to lock a row, if the row is already locked by another transaction, an error is returned immediately instead of waiting for the lock to be released.
When using the
SELECT ... FOR UPDATE SKIP LOCKEDclause:When a transaction attempts to lock a row, if the row is already locked by another transaction, the transaction skips that row and proceeds to the next one.
Example
Create a sample table and insert test data.
Create the
fruit_ordertable.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', 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,'John Doe',13.11,'2019'), (1011,'Zhang San',22.21,'2020'), (1011,'John Doe',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');
Lock query results by using FOR UPDATE
Execute the following statement to disable automatic commit.
SET GLOBAL autocommit = 0;For more information about the automatic commit feature, see autocommit.
Notice
Setting global variables is ineffective for the current session. The changes take effect only after you log out and log in again to establish a new session.
In session 1, execute the following statement to lock the query result with order ID 7.
obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE;The query result is as follows:
+----------+---------+-----------+-------------+------------+ | order_id | user_id | user_name | fruit_price | order_year | +----------+---------+-----------+-------------+------------+ | 7 | 1022 | Li Si | 34.44 | 2021 | +----------+---------+-----------+-------------+------------+ 1 row in setIn session 2, execute the following statement to update the
fruit_pricevalue in the row with order ID 7 to 16.15. This SQL statement waits until the preceding transaction is rolled back or theCOMMITstatement is executed; otherwise, it returns an error after the lock wait times out.obclient [test]> UPDATE fruit_order SET fruit_price = 16.15 WHERE order_id = 7;The query result is as follows:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionIn session 1, execute the following statement to commit the transaction.
COMMIT;In session 2, execute the following statement again to update the
fruit_pricevalue in the row with order ID 7 to 16.15.obclient [test]> UPDATE fruit_order SET fruit_price = 16.15 WHERE order_id = 7;The query result is as follows:
Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0In session 2, execute the following statement to commit the transaction.
COMMIT;In session 1, execute the following statement to query the updated data.
obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7;The query 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
Lock query results by using the NOWAIT or SKIP LOCKED option
In session 1, execute the following statement to lock the query result with order ID 7 using
FOR UPDATE:obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE;The query result is as follows:
+----------+---------+-----------+-------------+------------+ | order_id | user_id | user_name | fruit_price | order_year | +----------+---------+-----------+-------------+------------+ | 7 | 1022 | Li Si | 16.15 | 2021 | +----------+---------+-----------+-------------+------------+ 1 row in setIn session 2, execute the following statement to lock the query result with order ID 7 using
FOR UPDATE NOWAIT:obclient [test]> SELECT * FROM fruit_order WHERE order_id = 7 FOR UPDATE NOWAIT;The query result is as follows:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionIn session 3, execute the following statement to lock the query results where the order ID is greater than or equal to 7 using
FOR UPDATE SKIP LOCKED:obclient [test]> SELECT * FROM fruit_order WHERE order_id >= 7 FOR UPDATE SKIP LOCKED;The query 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