Solution on PostgreSQL database lock table

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)