The FOR UPDATE operator is used to lock data in a table.
OceanBase AI Database supports the FOR UPDATE and MULTI FOR UPDATE operators.
The general process for executing a query with the FOR UPDATE operator is as follows:
First, execute the
SELECTstatement part to obtain the query result set.Lock the records related to the query result set.
FOR UPDATE
FOR UPDATE is used to lock a single table (or a single partition).
In the following example, query Q1 locks the rows in table t1 that satisfy c1 = 1. Table t1 is a single-partition table, so operator 1 generates a FOR UPDATE operator.
obclient> CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES(1, 1);
Query OK, 0 rows affected
obclient> INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected
obclient> INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected
obclient> INSERT INTO t2 VALUES(1, 1);
Query OK, 1 rows affected
obclient> INSERT INTO t2 VALUES(2, 2);
Query OK, 1 rows affected
obclient> INSERT INTO t2 VALUES(3, 3);
Query OK, 1 rows affected
Q1:
obclient> EXPLAIN SELECT * FROM t1 WHERE c1 = 1 FOR UPDATE;
Query Plan:
=====================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-------------------------------------
|0 |MATERIAL | |10 |856 |
|1 | FOR UPDATE | |10 |836 |
|2 | TABLE SCAN|T1 |10 |836 |
=====================================
Outputs & filters:
-------------------------------------
0 - output([T1.C1], [T1.C2]), filter(nil)
1 - output([T1.C1], [T1.C2]), filter(nil), lock tables(T1)
2 - output([T1.C1], [T1.C2], [T1.__pk_increment]), filter([T1.C1 = 1]),
access([T1.C1], [T1.C2], [T1.__pk_increment]), partitions(p0)
In the preceding example, the outputs & filters section of the execution plan for query Q1 shows the output information of the FOR UPDATE operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the FOR UPDATE operator, so the value is nil. |
| lock tables | The tables that need to be locked. |
MULTI FOR UPDATE
MULTI FOR UPDATE is used to lock multiple tables (or multiple partitions).
In the following example, query Q2 locks the data in tables t1 and t2, targeting the rows that satisfy c1 = 1 AND c1 = d1. Since rows from multiple tables need to be locked, operator 1 is a MULTI FOR UPDATE.
obclient> CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE t2 (d1 INT, d2 INT);
Query OK, 0 rows affected
obclient> EXPLAIN SELECT * FROM t1, t2 WHERE c1 = 1 AND c1 = d1 FOR UPDATE;
Query Plan:
=====================================================
|ID|OPERATOR |NAME|EST. ROWS|COST|
-----------------------------------------------------
|0 |MATERIAL | |10 |931 |
|1 | MULTI FOR UPDATE | |10 |895 |
|2 | NESTED-LOOP JOIN CARTESIAN| |10 |895 |
|3 | TABLE GET |T2 |1 |52 |
|4 | TABLE SCAN |T1 |10 |836 |
=====================================================
Outputs & filters:
-------------------------------------
0 - output([T1.C1], [T1.C2], [T2.D1], [T2.D2]), filter(nil)
1 - output([T1.C1], [T1.C2], [T2.D1], [T2.D2]), filter(nil), lock tables(T1, T2)
2 - output([T1.C1], [T1.C2], [T2.D1], [T2.D2], [T1.__pk_increment]), filter(nil),
conds(nil), nl_params_(nil)
3 - output([T2.D1], [T2.D2]), filter(nil),
access([T2.D1], [T2.D2]), partitions(p0)
4 - output([T1.C1], [T1.C2], [T1.__pk_increment]), filter([T1.C1 = 1]),
access([T1.C1], [T1.C2], [T1.__pk_increment]), partitions(p0)
In the preceding example, the outputs & filters section of the execution plan for query Q2 shows the information of the MULTI FOR UPDATE operator as follows:
Information name |
Description |
|---|---|
| output | The output columns of this operator. |
| filter | The filter predicate of this operator. In the example, no filter is set for the MULTI FOR UPDATE operator, so the value is nil. |
| lock tables | The tables that need to be locked. |