Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PostgreSQL query lock and kill the process     - High-performance JavaScript reflows and repaints (Programming)

- Nginx-1.9.7 TCP reverse proxy (Server)

- Spring Data study notes -Helloworld (Programming)

- Linux terminal program running in the background (Linux)

- X security settings in Ubuntu (Linux)

- Linux iptables firewall and vsftpd to resolve the issue (Linux)

- C # using the HttpClient Note: Preheat the long connection (Programming)

- Linux command in the dialog whiptail (Linux)

- Docker improve safety (Server)

- Ubuntu 14.04 compile, install, configure, the latest development version GoldenDict (Linux)

- Java call by value and by reference (Programming)

- Linux unpack the tar file to a different directory (Linux)

- Binary tree and some basic operations with binary list (Programming)

- Linux System Getting Started Tutorial: Installing Brother printer in Linux (Linux)

- MySQL tmpdir parameter modification (Database)

- DRBD rapid installation and deployment (Server)

- Camera-based face recognition OpenCV crawl and storage format (Python) (Linux)

- How to install Git client in Ubuntu (Linux)

- Docker deployment practices in Ubuntu (Server)

- Linux, modify / retrieve the root password (Linux)

 
         
  PostgreSQL query lock and kill the process
     
  Add Date : 2018-11-21      
         
         
         
  Lookup table locks exist

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 the existence of a lock on a table query SQL statements.

After the discovery found in the existence of the lock, as follows:

 locktype | database | pid | mode | relation | relname

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

 relation | 439791 | 26752 | AccessShareLock | 2851428 | table_name

 relation | 439791 | 26752 | ExclusiveLock | 2851428 | table_name

Then check out according to the above table pg_stat_activity pid to inquiries about the lock 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)

Through the above can be found, that is causing the lock above statement has been hanging in there. Then after the end of the lock out, the application quickly finish.

Then apply the verification code, the code found inside the two transactions were not commit. After submission of the increased operations, the number of re-runs quickly finish.

If you want to kill, first make sure that statement is to the relevant personnel for critical processes.

Kill Methods: PG mydb database server, and then query the process PID 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
     
         
         
         
  More:      
 
- Firewall types and instructions (Linux)
- Service manager OpenBSD on rccl (Server)
- Grub2: Save Your bootloader (Linux)
- Automate deployment of Docker-based Rails applications (Server)
- numpy and SciPy installation under Python for scientific computing package (Linux)
- MySQL and MariaDB traditional master-slave cluster configuration (Database)
- Linux productivity tools and tips (Linux)
- Cool Android realization SVG animation (Programming)
- Linux operating system security can not be ignored (Linux)
- Detailed LVM2 (Linux)
- MySQL query plan key_len know all (Database)
- Linux kernel update error, update-initramfs: failed Solution (Linux)
- CentOS 6.4 compiler installed MySQL 5.6.14 (Database)
- Nmcli based network management command-line tool (Linux)
- Linux command -nohup & (Linux)
- Thinking in Java study notes - everything is an object (Programming)
- CentOS directory structure Explanation (Linux)
- Intrusion analysis and prevention tools Knark under Linux platform (Linux)
- Red Hat Enterprise Linux configuration VNC multi-user access methods (Linux)
- Java interface and inheritance (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.