Lock Related Issues

Sometimes, a rare situation can occur where a lock is never freed.

The workflow for troubleshooting locks is:

1. Identify which statement has obtained locks

2. Understand if the statement is itself stuck, or waiting for another statement

3. Try to abort the offending statement

4. 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

Tip

RELEASE_DEFUNCT_LOCKS has an optional input parameter to specify the number of seconds, after which RELEASE_DEFUNCT_LOCKS will execute.

Warning

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.