LockRows

Description

The LockRows operator is used to lock rows in a query result set to prevent other transactions from modifying or deleting these rows.

Typical Scenarios

• When SELECT ... FOR SHARE/UPDATE is used to lock rows in a transaction, other transactions cannot modify or delete these rows.
• When FOR SHARE is used to lock a row, the current transaction and other transactions cannot modify or delete the locked row.
• When FOR UPDATE is used to lock a row, no transaction can modify or delete the locked row except the current transaction.

Examples

SELECT FOR UPDATE syntax.

-- Prepare data.
gaussdb=#DROP TABLE IF EXISTS t;
gaussdb=#CREATE TABLE t(a int, b int, c int); 
CREATE TABLE 
gaussdb=#INSERT INTO t VALUES(generate_series(1, 10), generate_series(601, 610), generate_series(901, 910)); 
INSERT 0 10

-- Execution result.
gaussdb=#EXPLAIN SELECT * FROM t WHERE a = 1 FOR UPDATE; 
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=0.06..13.26 rows=1 width=18)
   Node/s: All datanodes
   ->  LockRows  (cost=0.00..13.16 rows=1 width=18)
         ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..13.16 rows=1 width=18)
               Spawn on: datanode1
               ->  Seq Scan on t  (cost=0.00..13.16 rows=1 width=18)
                     Filter: (a = 1)
(7 rows)

-- Drop.
gaussdb=#DROP TABLE IF EXISTS t;

Example: SELECT FOR SHARE syntax.

-- Prepare data.
gaussdb=# DROP TABLE IF EXISTS t;
gaussdb=# CREATE TABLE t(a int, b int, c int); 
CREATE TABLE 
gaussdb=# INSERT INTO t VALUES(generate_series(1, 10), generate_series(601, 610), generate_series(901, 910)); 
INSERT 0 10

-- Execution result.
gaussdb=# EXPLAIN SELECT * FROM t WHERE a = 1 FOR SHARE; 
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Streaming (type: GATHER)  (cost=0.06..13.26 rows=1 width=18)
   Node/s: All datanodes
   ->  LockRows  (cost=0.00..13.16 rows=1 width=18)
         ->  Streaming(type: REDISTRIBUTE)  (cost=0.00..13.16 rows=1 width=18)
               Spawn on: datanode1
               ->  Seq Scan on t  (cost=0.00..13.16 rows=1 width=18)
                     Filter: (a = 1)
(7 rows)

-- Drop.
gaussdb=# DROP TABLE t;

In the preceding example, the output of the LockRows operator is as follows.