PostgreSQL lock Query and kill process description

Query for locks that exist in the table

Select A.locktype,a.database,a.pid,a.mode,a.relation,b.relname

From Pg_locks A

Join Pg_class b on a.relation = B.oid

where upper (b.relname) = ' table_name ';

The above is an SQL statement that queries for locks on a table.

After finding out that there is a real lock, as follows:

LockType |  Database |      PID | mode | Relation | Relname

----------+----------+-------+-----------------+----------+---------

Relation | 439791 | 26752 |  Accesssharelock | 2851428 |table_name

Relation | 439791 | 26752 |  Exclusivelock | 2851428 |table_name

Then according to the PID found above to table pg_stat_activity query the corresponding SQL statement:

Select Usename,current_query, query_start,procpid,client_addr from pg_stat_activity where procpid=17509;

As follows:

Usename |          Current_query | Query_start |  Procpid | Client_addr

-----------+--------------------------------------------------------------------------------------------------- ------------+-------------------------------+---------+----------------

Gpcluster | DELETE from table_name WHERE A = 1 |   2011-05-14 09:35:47.721173+08 | 17509 | 192.168.165.18

(1 row)

The above can be found that the above lock causes the statement to hang there. After the lock is closed, the application runs out quickly.

Then check the code of the application and find that there are two transactions in the code that do not have a commit operation. After adding the commit operation, rerun the number and run out quickly.

If you want to kill, first confirm to the relevant person whether the statement is a critical process.

Kill method: In the PG Database MYDB server, query the process PID and then kill off.

> Ps-ef|grep 17509

Postgres 17509 4868 1 Nov18? 00:11:19 postgres:postgres mydb 192.168.165.18 (56059) SELECT

Postgres 30832 30800 0 15:18 pts/3 00:00:00 grep 17509

> Ps-ef|grep 17509

Postgres 17509 4868 1 Nov18? 00:11:19 postgres:postgres mydb 192.168.165.18 (56059) SELECT

Postgres 30838 30800 0 15:19 pts/3 00:00:00 grep 17509

> Kill-9 17509

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1714737

PostgreSQL lock Query and kill process description