Troubleshooting
Problem
When you try to unlock a database with the "ALTER DATABASE
Cause
A session locked the database, then ended without unlocking it. Later the postgres process id for that session was reused by a new session, and this session is seen as holding the active lock.
Environment
NPS 7.0.2
Diagnosing The Problem
Review all the postgres logs and look for the following scenario:
1. [Session 1] locks the database with "ALTER DATABASE <database name> LOCK FOR UPDATE", the postgres process id was "postgres pid 10000".
2. [Session 1] terminates or exits normally without unlocking the database.
3. [Session 2] starts and has the same postgres process id (10000).
4. [Session 3] tries to unlock the database with "ALTER DATABASE <database name> UNLOCK" command and gets the alterdbunlock: database is locked by 10000 error.
Resolving The Problem
The solution is as follows:
1. Check if there is a database lock on <database name>:
SELECT DBLOCKPID FROM _T_DATABASE WHERE DATNAME = <database name> ;
2. If the above select returns a row, set the DBLOCKPID column of _T_DATABASE to null:
UPDATE _T_DATABASE SET DBLOCKPID = NULL WHERE DATNAME = <database name>;
3. Try lock again.
ALTER DATABASE <database name> LOCK FOR UPDATE
Was this topic helpful?
Document Information
Modified date:
17 October 2019
UID
swg21683695