Description of PostgreSQL lock query and killing process _ MySQL

Query the selecta lock in the table. locktype,. database,. pid,. mode,. relation, B. relnamefrompg_locksajoinpg_classbona.relationb.oidwhereupper (B. relname) #39; TABLE_NAME #39; the above is query... query the locks in the table.

select a.locktype,a.database,a.pid,a.mode,a.relation,b.relnamefrom pg_locks ajoin pg_class b on a.relation = b.oidwhere upper(b.relname) = 'TABLE_NAME';

The preceding SQL statement is used to query whether a table has a lock.

The lock exists as follows:

 locktype | database |  pid  |      mode       | relation | relname----------+----------+-------+-----------------+----------+--------- relation |   439791 | 26752 | AccessShareLock |  2851428 |table_name relation |   439791 | 26752 | ExclusiveLock   |  2851428 |table_name

Then, query the SQL statement corresponding to the lock in the pg_stat_activity table based on the pid found above:

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)

Through the above, we can find that the above Lock keeps the statement hanging there. After the lock is completed, the application will soon run out.

Then, check the application code and find that no two transactions are committed in the code. After adding the submit operation, re-run the number, and soon run.

If you want to kill the statement, first confirm with the relevant personnel whether the statement is a key process.

Killing method: query the PID of the process on the mydb server of the PostgreSQL database and Kill it.

 > ps -ef|grep 17509postgres 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 17509postgres 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

The above is the description of the PostgreSQL lock query and kill process _ MySQL. For more information, see PHP Chinese network (www.php1.cn )!