Error LOCK_WAIT_TIMEOUT Is Reported When VACUUM FULL Is Executed

Symptom

The following error is reported when the VACUUM FULL command is executed:

1
2
3

[0]ERROR: dn_6009_6010: Lock wait timeout: thread 140158632457984 on node dn_6009_6010 waiting for AccessExclusiveLock on relation 2299036 of database 14522 after 1202001.968 ms
Detail: blocked by hold lock thread 140150147380992, statement <<backend information not available>>, hold lockmode AccessShareLock.
Line Number: 1

Possible Causes

"Lock wait timeout" in the log indicates that the lockwait times out. 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.

VACUUM FULL command execution may cause this error. For example, if you run the command over the entire database, the execution time may be long and may time out.

Handling Procedure

Run the VACUUM FULL command over a single table. The command format is VACUUM FULL table name. In addition, increase the frequency for running the command. Especially for tables that are frequently added, deleted, or modified, run the VACUUM FULL command periodically.