From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Victor Sterpu <victor(at)caido(dot)ro>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Lock problem |
Date: | 2014-04-02 15:29:17 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70EC7A9C3A05@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Victor Sterpu
Sent: Wednesday, April 02, 2014 11:19 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Lock problem
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_pid
blocked_user
blocking_statement
blocking_duration
blocking_pid
blocking_user
blocked_statement
blocked_duration
10665
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null, NULL, NULL )
00:47:33.995919
9844
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null, null, NULL, NULL )
00:37:36.175607
9844
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null, null, NULL, NULL )
00:37:36.175607
10665
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null, NULL, NULL )
00:47:33.995919
10665
postgres
<IDLE> in transaction
00:55:42.876538
9830
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null, null, NULL, NULL )
00:37:36.175607
10680
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null, null, NULL, NULL )
00:37:36.175607
10665
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1075033, 248.54, 10,17,0, now(), now(), NULL, null, null, NULL, NULL )
00:31:47.211123
9844
postgres
<IDLE> in transaction
00:55:42.876538
9830
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null, NULL, NULL )
00:47:33.995919
10706
postgres
INSERT INTO paraclinic_results VALUES (DEFAULT,1074986, 10.4, 15,15,0, now(), now(), NULL, null, null, NULL, NULL )
00:47:33.995919
9844
postgres
INSERT 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 made by postgresql.
I use transactional support a lot.
Can someoane give some advice about how can I prevent this locking?
Thank you.
So, did you check (in pg_stat_activity) what pid 9830 is doing, because looks like this session is holding other sessions.
I don't see " recursive lock" in your query output.
Regards,
Igor Neyman
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2014-04-02 15:31:13 | Re: Lock problem |
Previous Message | Victor Sterpu | 2014-04-02 15:19:05 | Lock problem |