From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Victor Sterpu Sent: Wednesday, April 02, 2014 11:19 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
