Lock Wait Detection

Updated on 2025-01-06 GMT+08:00

View PDF

Scenario

In job development, locks in database transaction management generally refer to table-level locks. GaussDB(DWS) supports eight lock modes, ranging from 1 to 8 based on exclusive levels. Each lock mode conflicts with another lock mode. Table 1 describes lock conflicts details.

Example: user u1 holds the RowExclusiveLock lock when executing the INSERT transaction on table test. If user u2 performs the VACUUM FULL transaction on the table test, a lock conflict occurs, and the lock of user u2 will wait.

Common lock wait detection is performed by querying the pgxc_lock_conflicts, pgxc_stat_activity, pgxc_thread_wait_status, and pg_locks views. The pgxc_lock_conflicts view is supported in versions later than 8.1.x. The detection method varies depending on the cluster version.

**Table 1** Lock conflicts
No.	Lock	Purpose	Conflict
1	AccessShareLock	SELECT	8
2	RowShareLock	SELECT FOR UPDATE/FOR SHARE	7 \| 8
3	RowExclusiveLock	INSERT/UPDATE/DELETE	5 \| 6 \| 7 \| 8
4	ShareUpdateExclusiveLock	VACUUM	4 \| 5 \| 6 \| 7 \| 8
5	ShareLock	CREATE INDEX	3 \| 4 \| 6 \| 7 \| 8
6	ShareRowExclusiveLock	ROW SELECT...FOR UPDATE	3 \| 4 \| 5 \| 6 \| 7 \| 8
7	ExclusiveLock	BLOCK ROW SHARE/SELECT...FOR UPDATE	2 \| 3 \| 4 \| 5 \| 6 \| 7 \| 8
8	AccessExclusiveLock	DROP CLASS/VACUUM FULL	1 \| 2 \| 3 \| 4 \| 5 \| 6 \| 7 \| 8

Procedure

Creating a lock wait:

Open a new connection session, connect to the GaussDB(DWS) database as common user u1, and create a test table u1.test in SCHEMA u1.
1

CREATE TABLE test (id int, name varchar(50));

Start transaction 1 and perform the INSERT operation.

      
         START TRANSACTION;
INSERT INTO test VALUES (1, 'lily');

Open a new connection session, connect to the GaussDB(DWS) database as the system administrator dbadmin, and perform the VACUUM FULL operation. The statement is blocked.
1

VACUUM FULL u1.test;

Lock wait detection (8.1.x and later versions)

Open a new connection session, connect to the GaussDB(DWS) database as the system administrator dbadmin, and check lock conflicts in the pgxc_lock_conflicts view.

As shown in the following figure, if the value of granted is f, the VACUUM FULL statement is waiting for another lock. If granted is t, the INSERT statement holds the lock. nodename: indicates the CN or DN where the lock is generated, for example, cn_5001.
1

SELECT * FROM pgxc_lock_conflicts;
Decide whether to terminate the lock based on the statement content. To terminate the lock, run the following statement: Obtain the value of pid from 1. In the preceding command, cn_5001 indicates the nodename queried in the preceding step.
1

execute direct on (cn_5001) 'SELECT PG_TERMINATE_BACKEND(pid)';

Lock wait detection (8.0.x and earlier versions)

Run the following statement in the database to obtain the value of query_id corresponding to the VACUUM FULL operation:

      
         SELECT * FROM pgxc_stat_activity WHERE query LIKE '%vacuum%'AND waiting = 't';

Click to enlarge

Run the following statement based on the obtained query_id to check whether lock wait exists and obtain the corresponding tid: In the preceding information, query_id is obtained from 1.
1

SELECT * FROM pgxc_thread_wait_status WHERE query_id = {query_id};
If acquire lock is displayed in wait_status in the command output, lock wait exists. Check the value of node_name and record it, for example, cn_5001 or dn_600x_600y.

Run the following statement to check the lock waited by the VACUUM FULL operation in pg_locks: The following uses cn_5001 as an example. If the lock wait is on a DN, change it to the corresponding DN name. pid is obtained in 2.

Record the value of relation in the command output.

      
         execute direct on (cn_5001) 'SELECT * FROM pg_locks WHERE pid = {tid} AND granted = ''f''';

Click to enlarge

Query the pg_locks system catalog for the PID of the lock based on the relation, which is obtained in 3.

      
         execute direct on (cn_5001) 'SELECT * FROM pg_locks WHERE relation = {relation} AND granted = ''t''';

Click to enlarge

Run the following statement based on the PID to query the corresponding SQL statement: The value of pid is obtained in 4.

      
         execute direct on (cn_5001) 'SELECT query FROM pg_stat_activity WHERE pid={pid}';

Click to enlarge

Based on the statement content, determine whether to stop the statement or run VACUUM FULL again after the statement is complete. To terminate the lock, run the following statement: The value of pid is obtained in 4.

After the lock is terminated, run VACUUM FULL again.
1

execute direct on (cn_5001) 'SELECT PG_TERMINATE_BACKEND(pid)';