All Products
Search
Document Center

FOR UPDATE

Last Updated: Jun 18, 2021

The FOR UPDATE operator locks certain data in a table.

FOR UPDATE and MULTI FOR UPDATE operators are two types of FOR UPDATE operators supported by OceanBase Database.

The FOR UPDATE operator performs a query by following this general process:

  1. Executes the SELECT statement to obtain the query result set.

  2. Locks 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, the purpose of query Q1 is to lock rows in table t1 that satisfy c1 = 1. As table t1 consists of a single partition, a FOR UPDATE operator is generated as the No. 1 operator.

obclient>CREATE TABLE t1(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>CREATE TABLE t2(c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(1, 1);
Query OK, 0 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t1 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t2 VALUES(1, 1);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t2 VALUES(2, 2);
Query OK, 1 rows affected (0.12 sec)

obclient>INSERT INTO t2 VALUES(3, 3);
Query OK, 1 rows affected (0.12 sec)

Q1: 
obclient> EXPLAIN SELECT * FROM t1 WHERE c1 = 1 FOR UPDATE\G;
*************************** 1. row ***************************
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 in the execution plan display of query Q1 shows in detail the output information of the FOR UPDATE operator.

Field

Description

output

The output expression of the operator.

filter

The filter conditions of the operator.

In this example, the condition is set to nil because no filter is configured for the FOR UPDATE operator.

lock tables

The tables in which locks are to be applied.

MULTI FOR UPDATE

MULTI FOR UPDATE allows you to apply locks to multiple tables or partitions.

In the following example, the purpose of query Q2 is to lock rows of tables t1 and t2 that satisfy c1 = 1 AND c1 = d1. In order to lock rows in multiple tables, a MULTI FOR UPDATE operator is generated as the No. 1 operator.

obclient>CREATE TABLE t1 (c1 INT, c2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>CREATE TABLE t2 (d1 INT, d2 INT);
Query OK, 0 rows affected (0.12 sec)

obclient>EXPLAIN SELECT * FROM t1, t2 WHERE c1 = 1 AND c1 = d1 
                FOR UPDATE\G;
*************************** 1. row ***************************
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 in the execution plan display of 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, the condition is set to nil because no filter is configured for the MULTI FOR UPDATE operator.

lock tables

The tables in which locks are to be applied.