From: | "Victor Sterpu" <victor(at)caido(dot)ro> |
---|---|
To: | pgeu-general(at)postgresql(dot)org |
Subject: | Lock problem |
Date: | 2014-04-02 15:15:07 |
Message-ID: | em371a9929-07c8-414e-8639-d1452a7ff7ad@victor-pc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgeu-general |
Hello
I have a problem that it seems to be very hard to debug.
Problem is from some postgresql locks. I use PostgreSQL 9.1.8.
I runned this query to fid the locks:
SELECT bl.pid AS blocked_pid, a.usename AS blocked_user, kl.pid AS
blocking_pid, ka.usename AS blocking_user, a.current_query AS
blocked_statement FROM pg_catalog.pg_locks bl JOIN
pg_catalog.pg_stat_activity a ON a.procpid = bl.pid JOIN
pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid
!= bl.pid JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
WHERENOT bl.granted;
The result is a recursive lock.
Pid 10665 is blocked by pid 9844 and pid 9844 is blocked by 10665.
These 2 inserts are in 2 separate transactions.
Can this be a postgresql bug?
blocked_pidblocked_userblocking_statementblocking_durationblocking_pidblocking_userblocked_statementblocked_duration10665postgresINSERT
INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(),
now(), NULL, null, null, NULL, NULL )00:47:33.9959199844postgresINSERT
INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(),
now(), NULL, null, null, NULL, NULL )00:37:36.1756079844postgresINSERT
INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(),
now(), NULL, null, null, NULL, NULL )00:37:36.17560710665postgresINSERT
INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(),
now(), NULL, null, null, NULL, NULL )00:47:33.99591910665postgres<IDLE>
in transaction00:55:42.8765389830postgresINSERT INTO paraclinic_results
VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null,
null, NULL, NULL )00:37:36.17560710680postgresINSERT INTO
paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(),
now(), NULL, null, null, NULL, NULL )00:37:36.17560710665postgresINSERT
INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(),
now(), NULL, null, null, NULL, NULL )00:31:47.2111239844postgres<IDLE>
in transaction00:55:42.8765389830postgresINSERT INTO paraclinic_results
VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null,
NULL, NULL )00:47:33.99591910706postgresINSERT INTO paraclinic_results
VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null,
NULL, NULL )00:47:33.9959199844postgresINSERT INTO paraclinic_results
VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null,
NULL, NULL )00:18:45.763758
I never use LOCK command in my application.
All locks are mede by postgresql.
I use transactional support a lot.
Can someoane give some advice about how can I prevent this locking?
Thank you.
From | Date | Subject | |
---|---|---|---|
Next Message | damien clochard | 2014-05-26 12:41:37 | Fwd: [OWF 2014] Call For Papers is open ! |
Previous Message | Patryk Kordylewski | 2014-02-04 11:41:48 | Re: PostgreSQL Europe 2014 Election results |