The handling of PostgreSQL deadlock

Today, a strange phenomenon is encountered, the Select and delete tables are executed normally, but the truncate and drop tables are always running without error.

Check some information to find out the cause of the problem, summarized as follows:

"Drop table" and "TRUNCATE TABLE" need to apply for exclusive lock "ACCESS EXCLUSIVE", the execution of this command is stuck, it shows that there are still operations on this table, such as query, etc.
Then only wait for this query operation to complete, "drop table" or "TRUNCATE TABLE" or add the field of SQL to get the "Access EXCLUSIVE" lock on this table, the operation can proceed.

1. Retrieves the ID of the deadlock process.

SELECT * from pg_stat_activity WHERE datname= ' deadlock database ID ';

In the retrieved field, the "wating" field, the one with the data T, is the deadlock process. Locate the value of the corresponding "procpid" column.

2. Kill the process.

SELECT pg_cancel_backend (' procpid value of the deadlock data ');

Result: After running, update the table again and SQL executes successfully.

If pg_stat_activity does not have a record, query pg_locks if there is a lock on this object

Select Oid,relname from Pg_class where relname= ' table name ';

Select locktype,pid,relation,mode,granted,* from pg_locks where relation= ' above queried OID ';

Select pg_cancel_backend (' process id ');

Another pg_terminate_backend () function can also kill a process.

The handling of PostgreSQL deadlock