Re: Lock problem

------ Original Message ------
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>
Sent: 4/2/2014 6:29:17 PM
Subject: RE: [GENERAL] Lock problem

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

No, I didn't look.
I will next time I have the problem.

Thank you.