Lock Related Issues
Sometimes, a rare situation can occur where a lock is never freed.
The workflow for troubleshooting locks is:
Identify which statement has obtained locks
Understand if the statement is itself stuck, or waiting for another statement
Try to abort the offending statement
Force the stale locks to be removed
For example, we will assume that the statement from the previous example is stuck (statement #
287). We can attempt to abort it using STOP_STATEMENT:
t=> SELECT STOP_STATEMENT(287); executed
If the locks still appear in the SHOW_LOCKS utility, we can force remove the stale locks:
t=> SELECT RELEASE_DEFUNCT_LOCKS(); executed
RELEASE_DEFUNCT_LOCKS has an optional input parameter to specify the number of seconds, after which
RELEASE_DEFUNCT_LOCKS will execute.
This operation can cause some statements to fail on the specific worker on which they are queued. This is intended as a “last resort” to solve stale locks.