[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_pidblocked_userblocking_statementblocking_durationblocking_pidblocking_userblocked_statementblocked_duration10665postgresINSERT 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.1756079844postgresINSERT 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.99591910665postgresIDLE 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.17560710680postgresINSERT 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.2111239844postgresIDLE 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.99591910706postgresINSERT 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.
Re: [GENERAL] Lock problem
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Victor Sterpu Sent: Wednesday, April 02, 2014 11:19 AM To: pgsql-general@postgresql.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_locksbl 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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
Victor Sterpu vic...@caido.ro writes: 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. You haven't actually explained what your problem is. 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; This query proves little. It might find two different transactions waiting for the same transactionid, but it doesn't show that one is waiting for the other. They could both be waiting for some third transaction. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu vic...@caido.ro wrote: 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_locksbl 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 IDLE in transaction IDLE in transaction is a locking red flag. It means your application has opened a transaction and is sitting there holding the transaction open. This is a very common cause of subtle application locking bugs. It can be legit if the application is doing heavy processing during a transaction or you simply raced to an idle transaction in pg_stat_activity, but in my experience 95%+ of the time it means transaction leakage which in turn leads to locking problems. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
-- Original Message -- From: Tom Lane t...@sss.pgh.pa.us To: Victor Sterpu vic...@caido.ro Cc: pgsql-general@postgresql.org Sent: 4/2/2014 6:31:13 PM Subject: Re: [GENERAL] Lock problem Victor Sterpu vic...@caido.ro writes: 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. You haven't actually explained what your problem is. 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; This query proves little. It might find two different transactions waiting for the same transactionid, but it doesn't show that one is waiting for the other. They could both be waiting for some third transaction. regards, tom lane Problem is that my application is hanging because of this locks and I can't point the problem. What query would prove more? All my transactions have commit or rollback. I don't know how to fix or how to begin to find the problem Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
-- Original Message -- From: Merlin Moncure mmonc...@gmail.com To: Victor Sterpu vic...@caido.ro Cc: PostgreSQL General pgsql-general@postgresql.org Sent: 4/2/2014 6:49:28 PM Subject: Re: [GENERAL] Lock problem On Wed, Apr 2, 2014 at 10:19 AM, Victor Sterpu vic...@caido.ro wrote: 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 IDLE in transaction IDLE in transaction is a locking red flag. It means your application has opened a transaction and is sitting there holding the transaction open. This is a very common cause of subtle application locking bugs. It can be legit if the application is doing heavy processing during a transaction or you simply raced to an idle transaction in pg_stat_activity, but in my experience 95%+ of the time it means transaction leakage which in turn leads to locking problems. merlin This is weird because all of my transaction have commit or rollback. I don't leave unterminated transactions. I can't see how this is happening. Is there a possibility that some network problems generate this problem? If this is the case is these some server protection for this situation? But why a unterminated transaction blocks all table operations? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
-- Original Message -- From: Igor Neyman iney...@perceptron.com To: Victor Sterpu vic...@caido.ro; pgsql-general@postgresql.org pgsql-general@postgresql.org Sent: 4/2/2014 6:29:17 PM Subject: RE: [GENERAL] Lock problem From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Victor Sterpu Sent: Wednesday, April 02, 2014 11:19 AM To: pgsql-general@postgresql.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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu vic...@caido.ro wrote: All my transactions have commit or rollback. Well, you have to verify that. There's a couple of ways to do it. Probably the most direct is to: 1. Make sure database is logging pids in log_line_prefix (this is a good idea all around) 2. turn on all statement logging (be advised: this can eat a lot of log space and slow down the server). Those two changes do not require a restart. A pg_ctl reload should be sufficient. Once you can do that, you should be able to locate database sessions per pg_stat_activity that are 'idle in transaction' for a long time without activity (anything over a second or so should be suspicious). That will give the pid which you can then use to grep through the statement log. Common culprits are: *) Dubious connection pooling solutions (php pconnect comes to mind) *) Bad error handling logic in application (say, badly handled thrown exception) merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
I followed all your advice and it is obiuos that this log will show exactly what I need to debug the situation. Great tip, thank you. -- Original Message -- From: Merlin Moncure mmonc...@gmail.com To: Victor Sterpu vic...@caido.ro Cc: PostgreSQL General pgsql-general@postgresql.org Sent: 4/2/2014 7:08:08 PM Subject: Re: [GENERAL] Lock problem On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu vic...@caido.ro wrote: All my transactions have commit or rollback. Well, you have to verify that. There's a couple of ways to do it. Probably the most direct is to: 1. Make sure database is logging pids in log_line_prefix (this is a good idea all around) 2. turn on all statement logging (be advised: this can eat a lot of log space and slow down the server). Those two changes do not require a restart. A pg_ctl reload should be sufficient. Once you can do that, you should be able to locate database sessions per pg_stat_activity that are 'idle in transaction' for a long time without activity (anything over a second or so should be suspicious). That will give the pid which you can then use to grep through the statement log. Common culprits are: *) Dubious connection pooling solutions (php pconnect comes to mind) *) Bad error handling logic in application (say, badly handled thrown exception) merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
There may pass a few days or weeks until next lock. But I don't undesrtand why the whole table is locked if there is one uncommited transaction. The following scenario might be the cause: 1. Transaction is started 2. the client application is closed because of a power surge, the started transaction will never be commited 3. from the server point of view there is a unfinished transaction that will block future statements Is this normal behaviour? -- Original Message -- From: Victor Sterpu vic...@caido.ro To: Merlin Moncure mmonc...@gmail.com Cc: PostgreSQL General pgsql-general@postgresql.org Sent: 4/2/2014 7:19:06 PM Subject: Re: [GENERAL] Lock problem I followed all your advice and it is obiuos that this log will show exactly what I need to debug the situation. Great tip, thank you. -- Original Message -- From: Merlin Moncure mmonc...@gmail.com To: Victor Sterpu vic...@caido.ro Cc: PostgreSQL General pgsql-general@postgresql.org Sent: 4/2/2014 7:08:08 PM Subject: Re: [GENERAL] Lock problem On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu vic...@caido.ro wrote: All my transactions have commit or rollback. Well, you have to verify that. There's a couple of ways to do it. Probably the most direct is to: 1. Make sure database is logging pids in log_line_prefix (this is a good idea all around) 2. turn on all statement logging (be advised: this can eat a lot of log space and slow down the server). Those two changes do not require a restart. A pg_ctl reload should be sufficient. Once you can do that, you should be able to locate database sessions per pg_stat_activity that are 'idle in transaction' for a long time without activity (anything over a second or so should be suspicious). That will give the pid which you can then use to grep through the statement log. Common culprits are: *) Dubious connection pooling solutions (php pconnect comes to mind) *) Bad error handling logic in application (say, badly handled thrown exception) merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
I'm sure is not right, but is a there a server side solution for such sitations? A configuration - timeout for idle transactions. -- Original Message -- From: Victor Sterpu vic...@caido.ro To: Victor Sterpu vic...@caido.ro; Merlin Moncure mmonc...@gmail.com Cc: PostgreSQL General pgsql-general@postgresql.org Sent: 4/2/2014 9:13:22 PM Subject: Re[2]: [GENERAL] Lock problem There may pass a few days or weeks until next lock. But I don't undesrtand why the whole table is locked if there is one uncommited transaction. The following scenario might be the cause: 1. Transaction is started 2. the client application is closed because of a power surge, the started transaction will never be commited 3. from the server point of view there is a unfinished transaction that will block future statements Is this normal behaviour? -- Original Message -- From: Victor Sterpu vic...@caido.ro To: Merlin Moncure mmonc...@gmail.com Cc: PostgreSQL General pgsql-general@postgresql.org Sent: 4/2/2014 7:19:06 PM Subject: Re: [GENERAL] Lock problem I followed all your advice and it is obiuos that this log will show exactly what I need to debug the situation. Great tip, thank you. -- Original Message -- From: Merlin Moncure mmonc...@gmail.com To: Victor Sterpu vic...@caido.ro Cc: PostgreSQL General pgsql-general@postgresql.org Sent: 4/2/2014 7:08:08 PM Subject: Re: [GENERAL] Lock problem On Wed, Apr 2, 2014 at 11:00 AM, Victor Sterpu vic...@caido.ro wrote: All my transactions have commit or rollback. Well, you have to verify that. There's a couple of ways to do it. Probably the most direct is to: 1. Make sure database is logging pids in log_line_prefix (this is a good idea all around) 2. turn on all statement logging (be advised: this can eat a lot of log space and slow down the server). Those two changes do not require a restart. A pg_ctl reload should be sufficient. Once you can do that, you should be able to locate database sessions per pg_stat_activity that are 'idle in transaction' for a long time without activity (anything over a second or so should be suspicious). That will give the pid which you can then use to grep through the statement log. Common culprits are: *) Dubious connection pooling solutions (php pconnect comes to mind) *) Bad error handling logic in application (say, badly handled thrown exception) merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Victor Sterpu Sent: Wednesday, April 02, 2014 2:25 PM To: Victor Sterpu; Merlin Moncure Cc: PostgreSQL General Subject: Re: [GENERAL] Lock problem I'm sure is not right, but is a there a server side solution for such sitations? A configuration - timeout for idle transactions. I don't think PG has such configuration parameter. But, you could easily write a function (say in PgPlSQL) and run it on schedule, where you could check IDLE IN TRANSACTION session and compare their start_time to system time, and then based on your criteria you could kill suspect session/transaction. But this could be dangerous; some long-running transactions could be perfectly valid. Regards, Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
On 02 Apr 2014, at 20:13, Victor Sterpu vic...@caido.ro wrote: There may pass a few days or weeks until next lock. But I don't undesrtand why the whole table is locked if there is one uncommited transaction. The following scenario might be the cause: 1. Transaction is started 2. the client application is closed because of a power surge, the started transaction will never be commited 3. from the server point of view there is a unfinished transaction that will block future statements Is this normal behaviour? I’m pretty sure that with such a powersurge the connection gets closed, causing the transaction to roll back. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
On Wed, Apr 2, 2014 at 3:01 PM, Igor Neyman iney...@perceptron.com wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- ow...@postgresql.org] On Behalf Of Victor Sterpu Sent: Wednesday, April 02, 2014 2:25 PM To: Victor Sterpu; Merlin Moncure Cc: PostgreSQL General Subject: Re: [GENERAL] Lock problem I'm sure is not right, but is a there a server side solution for such sitations? A configuration - timeout for idle transactions. I don't think PG has such configuration parameter. But, you could easily write a function (say in PgPlSQL) and run it on schedule, where you could check ILE IN TRANSACTDION session and compare their start_time to system time, and then based on your criteria you could kill suspect session/transaction. But this could be dangerous; some long-running transactions could be perfectly valid. I'd look for 'Idle In Transaction' backends that have clock_timestamp() - state_change x, where x is the maximum amount of time your application does stuff between queries while in transaction. Generally, x should never be more than about 10 seconds or so...or if it is, it's advisable to restructure your application so that more preprocessing is done before grabbing the transaction initially. In fact, for well written applications, seeing 'idle in transaction' should be quite exceptional. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock problem
I'm sorry Tom. This happens (at least) both in 8.4 and 9.0. I've just tested it in 2 different databases with the same results. I have workarounds to this, but I was wondering what could be the actual root of the problem, in order to (try to) achieve the best solution possible. Following is a test case with which you can reproduces the issue. Statements should be executed in the following order. -- session T1 means that the following lines should be executed from pg session 1. -- session T2 means that the following lines should be executed from pg session 2. create table b ( id bigint not null, x double precision, constraint pk_b primary key (id)); create table a ( id bigint not null, id_b bigint, x double precision, constraint pk_a primary key (id), constraint fk_b foreign key (id_b) references b (id)); insert into b (id, x) values (1, 0); insert into a (id, id_b, x) values (1, 1, 0); -- session T1 begin transaction; select * from a where id = 1 for update nowait; update a set x = x + 1 where id = 1; -- session T2 begin transaction; select * from b where id = 1 for update nowait; -- Query returned successfully: 1 row affected, 47 ms execution time. rollback; -- session T1 rollback; begin transaction; select * from a where id = 1 for update nowait; update a set x = x + 1 where id = 1; update a set x = x + 1 where id = 1; -- session T2 begin transaction; select * from b where id = 1 for update nowait; -- ERROR: could not obtain lock on row in relation b. SQL state: 55P03 Regarding my original question, I would like to know if this is a known issue/feature/bug/unwanted optimization consequence/or is it just a normal behavior that I should've had predicted. Best regards, Eduardo. On Wed, Sep 7, 2011 at 9:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: Eduardo Piombino drak...@gmail.com writes: I don't see how a new update to the same record in A, makes the difference to allow or deny the lock on a row on table B; I think it's probably explained by this: PS: The only relation between A and B is that A has a two FKs to B, but none of them are even included in the updates. IIRC there are some optimizations in the FK stuff that don't apply once a single transaction has updated a relevant row more than once. You haven't given enough details (not even a PG version) to be sure about it, but that's what I'd bet on. regards, tom lane
[GENERAL] Lock problem
Hello list, I'm having a locking problem and I'm not sure what is causing it. I have two pgsql concurrent transactions, running each in a separate connection to postgres (I can reproduce it from pgadmin). T1) operates only on table A begin transaction; select id from A where id = 100 for update nowait; update A set x = x + 15 where id = 100; *update A set x = x + 15 where id = 100; *commit; T2) operates only on table B begin transaction; select x from B where id = 116 for update nowait; update B set x = x + 1; commit; If I run transaction T1 up to the beginning of the second update, and then i stall there, transaction T2 is allowed to do the select for update with no problem at all. However, if transaction T1 goes a step further, and does the second update, from that point on, transaction T2 is not able to get the lock on B. I don't see how a new update to the same record in A, makes the difference to allow or deny the lock on a row on table B; This behaviour is backed up with a consistent increase in the locks from the server status views. I don't see how: select * from A for update nowait; update A set x = x + 1; has a different effect than (locks-wise) select * from A for update nowait; update A set x = x + 1; update A set x = x + 1; PS: The only relation between A and B is that A has a two FKs to B, but none of them are even included in the updates. I don't see how a second update (identical to the previous one if you wish) to A on T1 will prevent T2 from getting a row level lock on B. Does anyone have an explanation on why this happens? Thank you, Eduardo.
Re: [GENERAL] Lock problem
Eduardo Piombino drak...@gmail.com writes: I don't see how a new update to the same record in A, makes the difference to allow or deny the lock on a row on table B; I think it's probably explained by this: PS: The only relation between A and B is that A has a two FKs to B, but none of them are even included in the updates. IIRC there are some optimizations in the FK stuff that don't apply once a single transaction has updated a relevant row more than once. You haven't given enough details (not even a PG version) to be sure about it, but that's what I'd bet on. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] lock problem
hi Richard ,folks Question: is normal this locks ? which is the reason of this? thanks for your time best regards MDC --- Richard Huxton dev@archonet.com escribió: marcelo Cortez wrote: Richard Sorry for a delay question the select * from pg_stat_activity thows datid|datname|procpid|usesysid|usename|current_query|query_start 52800|sume|30124|1|postgres|IDLE|2005-09-01 13:30:02.921844-03 52800|sume|30125|1|postgres|IDLE|2005-09-01 13:37:21.631802-03 52800|sume|30186|1|postgres|SELECT c.actuacion_car AS c_actuacion, c.comentario1 || ' ' || c.comentario2 || ' ' || c.comentario3 AS c_comentario FROM caratult AS c INNER JOIN extractt AS t1 ON (c.id_extracto_car = t1.id_extracto) INNER JOIN repartit AS r1 ON (c.id_reparticion_uc = r|2005-09-01 13:35:45.152586-03 and the select * from pg_locks relation|database|transaction|pid|mode|granted 53046|52800||30186|AccessShareLock|t ||159274343|30125|ExclusiveLock|t 73744|52800||30186|AccessShareLock|t 16759|52800||30125|AccessShareLock|t 53094|52800||30186|AccessShareLock|t 73770|52800||30186|AccessShareLock|t ||159274288|30186|ExclusiveLock|t 73824|52800||30186|AccessShareLock|t 53054|52800||30186|AccessShareLock|t 73726|52800||30186|AccessShareLock|t 53074|52800||30186|AccessShareLock|t 53049|52800||30186|AccessShareLock|t 53127|52800||30186|AccessShareLock|t 9567503|52800||30186|AccessShareLock|t 74274|52800||30186|AccessShareLock|t this queries show locks into 30816 pid or a'im wrong? That's right - pid=30816 is the backend running the SELECT c.actuacion_car... query (see the pg_stat_activity output). The only other locks mentioned are for pid=30125, which I think are where you're executing SELECT * FROM pg_locks - bit puzzled as to why the relation/database columns are blank though. In short - I can't see anything blocking your query. What error message was telling you that locks were causing a problem? -- Richard Huxton Archonet Ltd ¡Llamá y ganá! Usá Yahoo! Messenger con Voz y participá del sorteo de un pasaje a cualquier lugar del mundo. Inscribite aquí: http://messenger.yahoo.com/ar/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] lock problem
marcelo Cortez wrote: hi Richard ,folks Question: is normal this locks ? which is the reason of this? As I said - I don't see any locks that could cause you problems. Can you provide the error message showing locks timing out? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] lock problem
Richard Sorry for the delay. question, the select * from pg_stat_activity thows datid|datname|procpid|usesysid|usename|current_query|query_start 52800|sume|30124|1|postgres|IDLE|2005-09-01 13:30:02.921844-03 52800|sume|30125|1|postgres|IDLE|2005-09-01 13:37:21.631802-03 52800|sume|30186|1|postgres|SELECT c.actuacion_car AS c_actuacion, c.comentario1 || ' ' || c.comentario2 || ' ' || c.comentario3 AS c_comentario FROM caratult AS c INNER JOIN extractt AS t1 ON (c.id_extracto_car = t1.id_extracto) INNER JOIN repartit AS r1 ON (c.id_reparticion_uc = r|2005-09-01 13:35:45.152586-03 and the select * from pg_locks relation|database|transaction|pid|mode|granted 53046|52800||30186|AccessShareLock|t ||159274343|30125|ExclusiveLock|t 73744|52800||30186|AccessShareLock|t 16759|52800||30125|AccessShareLock|t 53094|52800||30186|AccessShareLock|t 73770|52800||30186|AccessShareLock|t ||159274288|30186|ExclusiveLock|t 73824|52800||30186|AccessShareLock|t 53054|52800||30186|AccessShareLock|t 73726|52800||30186|AccessShareLock|t 53074|52800||30186|AccessShareLock|t 53049|52800||30186|AccessShareLock|t 53127|52800||30186|AccessShareLock|t 9567503|52800||30186|AccessShareLock|t 74274|52800||30186|AccessShareLock|t this queries show locks into 30816 pid or a'im wrong? best regards MDC --- Richard Huxton dev@archonet.com escribió: marcelo Cortez wrote: Hi Richard , folks the query is: SELECT c.actuacion_car AS c_actuacion, c.comentario1 || ' ' || c.comentario2 || ' ' || c.comentario3 AS c_comentario FROM caratult AS c INNER JOIN extractt AS t1 ON ...etc I'm not seeing anything here that should take locks, although it's late where I am. without table definition etc, you see are selects only. during this query, select * from pg_catalog.pg_locks show many rows with exclusive locks , and other activities or user's be freezed, Are you *sure* that it's this query? Could you show some of the rows from pg_locks along with corresponding rows from pg_stat_activity? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] lock problem
--- Richard Huxton dev@archonet.com escribió: marcelo Cortez wrote: Hi Richard , folks the query is: SELECT c.actuacion_car AS c_actuacion, c.comentario1 || ' ' || c.comentario2 || ' ' || c.comentario3 AS c_comentario FROM caratult AS c INNER JOIN extractt AS t1 ON ...etc I'm not seeing anything here that should take locks, although it's late where I am. without table definition etc, you see are selects only. during this query, select * from pg_catalog.pg_locks show many rows with exclusive locks , and other activities or user's be freezed, Are you *sure* that it's this query? Could you show some of the rows from pg_locks along with corresponding rows from pg_stat_activity? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] lock problem
hi all I have a lock problem, one select query freeze my application, the query in question is on complex select , with many join's, but select all of them. The select * from pg_catalog.pg_locks show many rows while the query freeze my aplication. the aplication use ODBC . any ideas?. any help wellcomed best MDC pd: what query show pg_locks ? , have a view ? sorry for my poor english and thanks for your time. __ Correo Yahoo! Espacio para todos tus mensajes, antivirus y antispam ¡gratis! ¡Abrí tu cuenta ya! - http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] lock problem
marcelo Cortez wrote: hi all I have a lock problem, one select query freeze my application, the query in question is on complex select An ordinary select doesn't take any locks. What is the actual query causing this? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] lock problem
Hi Richard , folks
the query is:
SELECT c.actuacion_car AS c_actuacion,
c.comentario1 || ' ' || c.comentario2 || ' ' ||
c.comentario3 AS c_comentario
FROM caratult AS c INNER JOIN extractt AS t1 ON
(c.id_extracto_car = t1.id_extracto)
INNER JOIN repartit AS r1 ON (c.id_reparticion_uc =
r1.id_reparticion)
INNER JOIN repartit AS r2 ON (t1.id_reparticion_ext =
r2.id_reparticion)
INNER JOIN repartit AS r3 ON (c.id_reparticion_car =
r3.id_reparticion)
LEFT JOIN carcallt AS l ON (c.actuacion_car =
l.actuacion_cal)
LEFT JOIN callest AS ll9 ON (l.id_calle_cal =
ll9.id_calle)
LEFT JOIN callest AS ll10 ON (l.id_calle1_cal =
ll10.id_calle)
LEFT JOIN callest AS ll11 ON (l.id_calle2_cal =
ll11.id_calle)
LEFT JOIN callest AS ll12 ON (l.id_esquina_cal =
ll12.id_calle)
LEFT JOIN pasest AS p ON (c.actuacion_car =
p.act_principal)
LEFT JOIN repartit AS r7 ON (p.id_repart_origen =
r7.id_reparticion)
LEFT JOIN repartit AS r8 ON (p.id_repart_destino =
r8.id_reparticion)
LEFT JOIN repartit AS r9 ON (p.id_reparticion_u =
r9.id_reparticion)
WHERE anio(c.actuacion_car)::integer = '2005'
::integer AND
DATE_PART('year', fecha_inicio_real) ::integer = 2005
AND
upper(repart(c.actuacion_car)) = upper('AGJ') LIMIT
101
without table definition etc, you see are selects
only.
during this query, select * from pg_catalog.pg_locks
show many rows with exclusive locks , and other
activities or user's be freezed,
best regards.
mdc
pd: other information
PostgreSQL 7.4.3 on i386-pc-linux-gnu, compiled by
GCC 2.95.4
kernel: Linux serverf 2.4.26-1-686-smp #1 SMP Fri Aug
20 19:39:10 CEST 2004 i686 unknown
Distro: Debian
--- Richard Huxton dev@archonet.com escribió:
marcelo Cortez wrote:
hi all
I have a lock problem, one select query freeze my
application, the query in question is on complex
select
An ordinary select doesn't take any locks. What is
the actual query
causing this?
--
Richard Huxton
Archonet Ltd
___
1GB gratis, Antivirus y Antispam
Correo Yahoo!, el mejor correo web del mundo
http://correo.yahoo.com.ar
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] lock problem
marcelo Cortez wrote: Hi Richard , folks the query is: SELECT c.actuacion_car AS c_actuacion, c.comentario1 || ' ' || c.comentario2 || ' ' || c.comentario3 AS c_comentario FROM caratult AS c INNER JOIN extractt AS t1 ON ...etc I'm not seeing anything here that should take locks, although it's late where I am. without table definition etc, you see are selects only. during this query, select * from pg_catalog.pg_locks show many rows with exclusive locks , and other activities or user's be freezed, Are you *sure* that it's this query? Could you show some of the rows from pg_locks along with corresponding rows from pg_stat_activity? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Lock problem on Solaris
When I start postmaster, I get the following: IpcSemaphoreCreate: semget failed (No space left on device) key = ... I know that this is caused by not having enough semaphores and can be fixed by increasing some settings. However, it normall works fine with the setting, and a nasty crash killed it this morning. I think that for some reason, some semaphores are still being held. I have ps -aux'd and killed all the postgresql processes, and have erased all my files in the /tmp directory. What else can I do? Thanks, Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer
Re: [GENERAL] Lock problem on Solaris
Travis Bauer [EMAIL PROTECTED] writes: When I start postmaster, I get the following: IpcSemaphoreCreate: semget failed (No space left on device) key = ... However, it normall works fine with the setting, and a nasty crash killed it this morning. I think that for some reason, some semaphores are still being held. Sounds likely. The standard utility programs ipcs and ipcrm will help you clean up --- see their man pages. (You probably will need to zap the old shared-memory segments as well as the old semaphores; these tools will handle both tasks.) Our distribution includes a shell script "ipcclean" that purports to do this for you, but I wouldn't trust it too far, since the output format of ipcs is somewhat platform-specific. regards, tom lane
Re: [GENERAL] Lock problem on Solaris
Tom, Thanks. Running ipcclean fixed it right away. Travis Bauer | CS Grad Student | IU |www.cs.indiana.edu/~trbauer On Mon, 9 Oct 2000, Tom Lane wrote: Travis Bauer [EMAIL PROTECTED] writes: When I start postmaster, I get the following: IpcSemaphoreCreate: semget failed (No space left on device) key = ... However, it normall works fine with the setting, and a nasty crash killed it this morning. I think that for some reason, some semaphores are still being held. Sounds likely. The standard utility programs ipcs and ipcrm will help you clean up --- see their man pages. (You probably will need to zap the old shared-memory segments as well as the old semaphores; these tools will handle both tasks.) Our distribution includes a shell script "ipcclean" that purports to do this for you, but I wouldn't trust it too far, since the output format of ipcs is somewhat platform-specific. regards, tom lane
