| |
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 |
|
|
|