Lock problem - Mailing list pgsql-general

From Victor Sterpu
Subject Lock problem
Date
Msg-id emfa2cc422-207b-424d-a611-f2b60bb010fd@victor-pc
Whole thread Raw
Responses Re: Lock problem
Re: Lock problem
Re: Lock problem
List pgsql-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_duration
10665postgresINSERT 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.175607
9844postgresINSERT 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.995919
10665postgres<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.175607
10680postgresINSERT 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.211123
9844postgres<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.995919
10706postgresINSERT 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 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.

pgsql-general by date:

Previous
From: Andrew Sullivan
Date:
Subject: Re: Insert zero to auto increment serial column
Next
From: Igor Neyman
Date:
Subject: Re: Lock problem