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=# 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                         
---------------------------------------------------------- 
 LockRows  (cost=0.00..34.41 rows=10 width=18) 
   ->  Seq Scan on t  (cost=0.00..34.31 rows=10 width=18) 
         Filter: (a = 1) 
(3 rows)

-- Drop.
gaussdb=# DROP TABLE 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
----------------------------------------------------------
 LockRows  (cost=0.00..34.41 rows=10 width=18)
   ->  Seq Scan on t  (cost=0.00..34.31 rows=10 width=18)
         Filter: (a = 1)
(3 rows)

-- Drop.
gaussdb=# DROP TABLE t;

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