Lock problem

["Victor Sterpu" <victor@xxxxxxxx>]

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.

 

DISCLAIMER:
Acest mesaj de posta electronica si documentele aferente sunt confidentiale. Este interzisa distribuirea, dezvaluirea sau orice alt mod de utilizare a lor. Daca nu sunteti destinatarul acestui mesaj, este interzis sa actionati in baza acestor informatii. Citirea, copierea, distribuirea, dezvaluirea sau utilizarea in alt mod a informatiei continute in acest mesaj constituie o incalcare a legii. Daca ati primit mesajul din greseala, va rugam sa il distrugeti, anuntand expeditorul de eroarea comisa. Intrucat nu poate fi garantat faptul ca posta electronica este un mod sigur si lipsit de erori de transmitere a informatiilor, este responsabilitatea dvs. sa va asigurati ca mesajul (inclusiv documentele alaturate lui) este validat si autorizat spre a fi utilizat in mediul dvs.