Locks

Overview

A database uses locks to control the concurrency of public resources. The general process of using a lock consists of three steps: lock addition, critical area operation, and lock release.

When a DDL/DML operation is performed on a table, the database locks the table and releases the table when the transaction ends. GaussDB provides eight levels of locks for concurrent execution of different statements. The following table lists the locks corresponding to each operation.

When the locks of two transactions conflict, the thread that fails to obtain the lock waits for the lock. If the wait time exceeds the value of lockwait_timeout (20 minutes by default), the lock wait times out. For details about other lock-related parameters, see "Configuring GUC Parameters > GUC Parameters > Lock Management" in Administrator Guide.

-- Create a table and insert data into the table.
gaussdb=# CREATE TABLE testl1(c1 INT, c2 VARCHAR(5));
CREATE TABLE
gaussdb=# INSERT INTO testl1 VALUES (1,'a'),(2,'b'),(3,'c');
INSERT 0 3

-- View session parameters.
gaussdb=# SHOW lockwait_timeout;
 lockwait_timeout 
------------------
 20min
(1 row)

-- Perform the operation in the first session.
gaussdb=# BEGIN;
BEGIN
gaussdb=# INSERT INTO testl1 VALUES (4,'d');
INSERT 0 1

-- Perform the operation in the second session. The SQL statement is executed only after the first session ends.
gaussdb=# CREATE INDEX idx_testl1_c1 ON testl1(c1);

-- End the transaction in the first session.
gaussdb=# END;
COMMIT

-- Drop the table.
gaussdb=# DROP TABLE testl1;
DROP TABLE

Querying Lock Waits

-- Create a table and insert data into the table.
gaussdb=# CREATE TABLE testl2(id INT,info VARCHAR(10));
CREATE TABLE
gaussdb=# INSERT INTO testl2 VALUES (1,'info1'),(2,'info2');
INSERT 0 2

-- Perform the operation in the first session.
gaussdb=# BEGIN;
BEGIN
gaussdb=# CREATE INDEX idx_testl2_id ON testl2(id);
CREATE INDEX

-- Perform the operation in the second session.
gaussdb=# INSERT INTO testl2 VALUES (3,'info3');

• Open a new session and run the following SQL statement to query the SQL statements to be executed:

  gaussdb=# SELECT datname,pid,query,query_id,waiting FROM pg_stat_activity WHERE waiting = TRUE;
  datname  |       pid       |                 query                  |     query_id     | waiting 
  ----------+-----------------+----------------------------------------+------------------+---------
   postgres | 140389444876032 | INSERT INTO testl2 VALUES (3,'info3'); | 3940649678789642 | t
  (1 row)

• Query wait events by PID.

  gaussdb=# SELECT node_name,db_name,query_id,tid,wait_status,wait_event FROM pg_thread_wait_status WHERE tid = 140389444876032 AND db_name IS NOT NULL;
   node_name | db_name  |     query_id     |       tid       | wait_status  | wait_event 
  -----------+----------+------------------+-----------------+--------------+------------
   dn_6001   | postgres | 3940649678789642 | 140389444876032 | acquire lock | relation
  (1 row)

• Run the following SQL statements to query PIDs of SQL statements that hold locks:

  -- Run the following statement to query PIDs of SQL statements that hold locks. The value of locktype is the same as that of wait_event.
  gaussdb=# SELECT relation,pid,mode,granted FROM pg_locks WHERE pid = 140389444876032 AND locktype='relation';
   relation |       pid       |       mode       | granted 
  ----------+-----------------+------------------+---------
      26466 | 140389444876032 | RowExclusiveLock | f
  (1 row)
  
  -- Run the following statement to query PIDs of SQL statements that hold locks. If the value of granted is t, the statement is a statement that holds a lock.
  gaussdb=# SELECT relation,pid,mode,granted FROM pg_locks WHERE relation = 26466;
   relation |       pid       |       mode       | granted 
  ----------+-----------------+------------------+---------
      26466 | 140389444876032 | RowExclusiveLock | f
      26466 | 140389939934976 | AccessShareLock  | t
      26466 | 140389939934976 | ShareLock        | t
  (3 rows)

• Run the following SQL statements to query details about SQL statements that hold locks:
  If the following information is displayed, the statement status is idle in transaction, indicating that the transaction is to be committed. After a transaction is manually committed, the INSERT statement starts to be executed.

  gaussdb=# SELECT datname,pid,query,query_id,waiting,state FROM pg_stat_activity WHERE pid = 140389939934976;
   datname  |       pid       |                   query                   | query_id | waiting |        state        
  ----------+-----------------+-------------------------------------------+----------+---------+---------------------
   postgres | 140389939934976 | CREATE INDEX idx_testl2_id ON testl2(id); |        0 | f       | idle in transaction
  (1 row)

  Determine whether to use the pg_terminate_backend function to end the SQL thread that holds the lock on the corresponding node according to the actual situation.

  Example:

  gaussdb=# SELECT pg_terminate_backend(140389939934976);
   pg_terminate_backend 
  ----------------------
   t
  (1 row)