During SQL Execution, a Table Deadlock Occurs and An Error Stating LOCK_WAIT_TIMEOUT Is Reported

Symptom

During SQL execution, lock wait timeout (LOCK_WAIT_TIMEOUT) is reported.

Possible Causes

Lock wait timeout is generally caused by the fact that another SQL statement has held the lock. The current SQL statement can be executed only after the SQL statement that holds the lock is successfully executed and releases the lock. If the lock wait time exceeds the specified value of the GUC parameter lockwait_timeout, the system reports the LOCK_WAIT_TIMEOUT error.

Handling Procedure

1. For clusters of 8.1.x or later, check lock conflicts in the pgxc_lock_conflicts view.

   SELECT * FROM pgxc_lock_conflicts;

   For clusters of 8.0.x and earlier versions, run the following SQL query to check whether there are blocked SQL statements. If yes, forcibly end the blocked SQL sessions.

   1 2 3 4 5 6 7 8 9 10 11

   SELECT w.query as waiting_query, w.pid as w_pid, w.usename as w_user, l.query as locking_query, l.pid as l_pid, l.usename as l_user, n.nspname || '.' || c.relname as tablename from pg_stat_activity w join pg_locks l1 on w.pid = l1.pid and not l1.granted join pg_locks l2 on l1.relation = l2.relation and l2.granted join pg_stat_activity l on l2.pid = l.pid join pg_class c on c.oid = l1.relation join pg_namespace n on n.oid=c.relnamespace where w.waiting;

2. After the blocked table and the schema information are found, end the session based on the session ID.

   1	SELECT PG_TERMINATE_BACKEND(PID);

3. This fault is generally caused by improper service scheduling. It is recommended that the scheduling time of each service be properly arranged.
4. You can also set the GUC parameter lockwait_timeout to control the maximum wait time (wait timeout) of a single lock.

   The unit of lockwait_timeout is millisecond. The default value is 20 minutes.

   The lockwait_timeout parameter is of the SUSET type. Follow the instructions in Configuring GUC Parameters to configure the parameter.