Description of PostgreSQL lock query and killing Process

Source: Internet
Author: User

Description of PostgreSQL lock query and killing Process

Query the locks in the table.

Select a. locktype, a. database, a. pid, a. mode, a. relation, B. relname

From pg_locks

Join pg_class B on a. relation = B. oid

Where 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

----------- + Response + ----------------------------- + --------- + ----------------

Gpcluster | delete from TABLE_NAME where a = 1 | 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 17509

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

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

> Ps-ef | grep 17509

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

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

> Kill-9 17509

------------------------------------ Lili split line ------------------------------------

Install PostgreSQL 6.3 on yum in CentOS 9.3

PostgreSQL cache details

Compiling PostgreSQL on Windows

Configuration and installation of LAPP (Linux + Apache + PostgreSQL + PHP) Environment in Ubuntu

Install and configure phppgAdmin on Ubuntu

Install PostgreSQL9.3 on CentOS

Configure a Streaming Replication cluster in PostgreSQL

How to install PostgreSQL 7/6 and phpPgAdmin in CentOS 5/6. 4

------------------------------------ Lili split line ------------------------------------

PostgreSQL details: click here
PostgreSQL: click here

This article permanently updates the link address:

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.