How to kill a locked process?

Preface

After a system iteration, the user complained that he could not log in to the system successfully. After testing and reproducing and log positioning, it was found that the user triggered a stack overflow exception after performing some operations on the ui, causing the data of the user login information table in the database to be locked and unable to be released.

This table stores the user's session information.

Although the problem was solved later, the data locked in the user's login information table in the database cannot be released, which causes the user to never be able to log in successfully. He needs to manually run SQL to remove the lock.

Kill the specified process

PostgreSQL provides two functions:pg_cancel_backend()andpg_terminate_backend(), the input parameters of these two functions are process PID. Assume that the process with process PID of 20407 is now to be killed.

How to use it is as follows:

select pg_cancel_backend(20407);

--Or you can execute this function：
select pg_terminate_backend(20407);

The difference between these two functions is as follows:

pg_cancel_backend()

1. Only the background process under the current user can be closed
2. Send SIGINT signal to the background to close the transaction. At this time, the session is still there and the transaction is rolled back

pg_terminate_backend()

1. Superuser permission is required, and all background processes can be closed
2. Send SIGTERM signal to the background to close the transaction. At this time, the session will also be closed and the transaction will be rolled back.

So how do you know which tables and which processes are locked?

You can use the following SQL to find it out:

select * from pg_locks a
join pg_class b on  = 
join pg_stat_activity c on  = 
where  like '%ExclusiveLock%';

The one we checked here is the exclusive lock, which can also be accurate to the line exclusive lock or shared lock.

Here are a few important columns:

• It's the process id
• It is a table name, constraint name or index name
• It's lock type

Kill the process of specifying locks in the specified table

select pg_cancel_backend() from pg_locks a
join pg_class b on  = 
join pg_stat_activity c on  = 
where  ilike 'Table name' 
and  like '%ExclusiveLock%';

--Or use more domineeringpg_terminate_backend()：
select pg_terminate_backend() from pg_locks a
join pg_class b on  = 
join pg_stat_activity c on  = 
where  ilike 'Table name' 
and  like '%ExclusiveLock%';

Another thing to note is:

pg_terminate_backend()The session will also be closed. At this time, the sessionId will be invalid, which may cause the system account to log out and the browser's cached cookies need to be cleared (at least this is the case encountered by our system).

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.