FOR UPDATE operators lock certain data in tables.
OceanBase Database supports the following types of FOR UPDATE operators: FOR UPDATE and MULTI FOR UPDATE.
FOR UPDATE operators perform a query by following this general process:
Execute the
SELECTstatement to obtain the query result set.Lock the records that correspond to the query result set.
FOR UPDATE
The FOR UPDATE operator allows you to apply locks in a single table or partition.
In the following example, query Q1 is to lock rows in table t1 that satisfy c1 = 1. Table t1 consists of a single partition. A FOR UPDATE operator is used as Operator 1.
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 for query Q1 shows in detail the output information of the FOR UPDATE operator.
| Field | Description |
|---|---|
| output | The output expressions of the operator. |
| filter | The filter conditions of the operator. In this example, filter is set to nil because no filter condition is configured for the FOR UPDATE operator. |
| lock tables | The tables to which locks are to be applied. |
MULTI FOR UPDATE
MULTI FOR UPDATE can lock data in multiple tables or partitions.
In the following example, query Q2 is to lock rows of tables t1 and t2 that satisfy c1 = 1 AND c1 = d1. To lock rows in multiple tables, a MULTI FOR UPDATE operator is used as Operator 1.
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 for query Q2 shows in detail the output information of the MULTI FOR UPDATE operator.
| Field | Description |
|---|---|
| output | The output columns of the operator. |
| filter | The filter conditions of the operator. In this example, filter is set to nil because no filter condition is configured for the MULTI FOR UPDATE operator. |
| lock tables | The tables to which locks are to be applied. |
