Solution on PostgreSQL database lock table

Pankaj kushwaha
1 min readJul 3, 2020

Solve the problem of PostgreSQL database lock table

Solution 1.

  1. Query whether the table is locked
select oid from pg_class where relname=’The table may be locked table’; select pid from pg_locks where relation=’The oid found above’;

If the query results, it means that the table is locked and you need to release the lock


select pg_cancel_backend (Pid found above)

Solution 2:

Postgresql query lock table and unlock the table

1. — Query the status and other information of ACTIVITY

select
T.PID, T.STATE, T.QUERY, T.WAIT_EVENT_TYPE, T.WAIT_EVENT,
T.QUERY_START
from PG_STAT_ACTIVITY T
where T.DATNAME =’database user name’;

2. — Query the ACTIVITY


select T.PID, T.STATE, T.QUERY, T.WAIT_EVENT_TYPE, T.WAIT_EVENT,
T.QUERY_START
from PG_STAT_ACTIVITY T
where T.DATNAME =’Database Username’
and T.WAIT_EVENT_TYPE =’Lock’;

3. Record the numeric value of the PID field of the second query statement and execute the following query statement to unlock:

Unlock the corresponding ACTIVITY by PID

select PG_CANCEL_BACKEND(‘6984’);

The above query statement executes the pg_cancel_backend() function, which is used to cancel background operations and roll back uncommitted things.

Thanks for reading this post, if you like you can follow me up(pankajconnect.com)

--

--

Pankaj kushwaha

Database/System Administrator | DevOPS | Cloud Specialist | DevOPS