PostgreSQL Deadlock Handling

Origin

When a strange phenomenon occurs, the Select and delete tables are executed normally, but the truncate and drop tables run all the time, and no error is encountered.

Reason

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

Solution

1. Retrieves the ID of the deadlock process.

Select Oid,relname from Pg_class where relname= ' all_data ';

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 locktype,pid,relation,mode,granted,* from where relation= ' oid ' above queried;

Kill the process

Select Pg_cancel_backend (' Process id ');

Another pg_terminate_backend () function can also kill a process.

Summarize

Select Locktype,database,pid,relation, mode from pg_locks where relation= above Oid;select * from pg_stat_activity where PID = Above Pidselect pg_terminate_backend (upper PID);

Delete Database said to be linked

Abnormal

db_v43=> drop Database test_db; ERROR:  Database "test_db" is being accessed by other Usersdetail:  There are 1 other session using the database.

Solve

Then you can delete the database.

PostgreSQL Deadlock Handling