Thread: how to release a transaction lock on a table?
Hello everyone. I am trying to do an ALTER TABLE, but it hangs indefinitely. I think the table is locked from a transaction, and in pg_locks I found: relation 75907 database 74861 pid 29604 mode AccessExclusiveLock granted f Is there a way to release this lock? Or does the database need to re-started? Si
On Mon, Jan 31, 2005 at 10:13:26AM -0800, Si Chen wrote: > Hello everyone. I am trying to do an ALTER TABLE, but it hangs > indefinitely. I think the table is locked from a transaction, and in > pg_locks I found: > relation 75907 > database 74861 > pid 29604 > mode AccessExclusiveLock > granted f Look for other processes that have a lock on the table: SELECT * FROM pg_locks WHERE relation = 75907; If you have stats_command_string turned on then you can query pg_stat_activity to see what the other processes are doing. > Is there a way to release this lock? Or does the database need to > re-started? Before taking drastic steps like restarting the database, find out who holds the conflicting lock and why. You might have an application sitting idle in a transaction that it should be committing or rolling back, in which case the application should probably be fixed. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Thanks, Michael, for the input. Is there anyway in PostgreSQL to force some transactions to close (rollback if necessary)? I notice there is no way to release a LOCK manually. Si Michael Fuhr wrote: >On Mon, Jan 31, 2005 at 10:13:26AM -0800, Si Chen wrote: > > > >>Hello everyone. I am trying to do an ALTER TABLE, but it hangs >>indefinitely. I think the table is locked from a transaction, and in >>pg_locks I found: >>relation 75907 >>database 74861 >>pid 29604 >>mode AccessExclusiveLock >>granted f >> >> > >Look for other processes that have a lock on the table: > >SELECT * FROM pg_locks WHERE relation = 75907; > >If you have stats_command_string turned on then you can query >pg_stat_activity to see what the other processes are doing. > > > >>Is there a way to release this lock? Or does the database need to >>re-started? >> >> > >Before taking drastic steps like restarting the database, find out >who holds the conflicting lock and why. You might have an application >sitting idle in a transaction that it should be committing or rolling >back, in which case the application should probably be fixed. > > >
On Mon, Jan 31, 2005 at 11:43:45AM -0800, Si Chen wrote: > Thanks, Michael, for the input. Is there anyway in PostgreSQL to force > some transactions to close (rollback if necessary)? I notice there is no > way to release a LOCK manually. I'm not aware of a way to terminate a transaction in another session short of killing its backend process. PostgreSQL 8.0 has a pg_cancel_backend() function, but it cancels only a backend's current query, not any outer transaction it might be part of. Have you discovered what's holding the conflicting lock? It would be prudent to understand what's happening before killing one transaction just so another can succeed. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Michael, I would like to track down what in the application is causing the deadlock, but it's a bit hard since it's a big app with lots going on. I can track down the PID of the transaction which is locking the tables, but is there anyway to go from the PID back to the SQL statement(s) in the transaction? Thanks, Si Michael Fuhr wrote: >On Mon, Jan 31, 2005 at 11:43:45AM -0800, Si Chen wrote: > > > >>Thanks, Michael, for the input. Is there anyway in PostgreSQL to force >>some transactions to close (rollback if necessary)? I notice there is no >>way to release a LOCK manually. >> >> > >I'm not aware of a way to terminate a transaction in another session >short of killing its backend process. PostgreSQL 8.0 has a >pg_cancel_backend() function, but it cancels only a backend's current >query, not any outer transaction it might be part of. > >Have you discovered what's holding the conflicting lock? It would >be prudent to understand what's happening before killing one >transaction just so another can succeed. > > >
On Tue, Feb 01, 2005 at 10:53:11AM -0800, Si Chen wrote: > I would like to track down what in the application is causing the > deadlock, Are you sure you understand what "deadlock" means? Deadlock occurs, for example, when connection A holds a lock that connection B wants and connection B holds a lock that connection A wants. PostgreSQL should recognize that situation and cause one of the connections to fail after a timeout (one second by default). That doesn't sound like what you're experiencing -- based on what you've said, one connection holds a lock and another is blocked waiting for it. > but it's a bit hard since it's a big app with lots going on. > I can track down the PID of the transaction which is locking the tables, > but is there anyway to go from the PID back to the SQL statement(s) in > the transaction? The query "SELECT * FROM pg_stat_activity" should show connections' current queries if you have stats_command_string set to "on". If stats_command_string is "off" then you can enable it by editing postgresql.conf and restarting the postmaster, but unfortunately that won't help you track down queries that are already running. Is it possible that the transaction holding the lock is idle? Some applications use long-lived transactions that can cause locking problems in other transactions. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi. Sorry about the confusion in terminology. You are right. The transactions are idle--when I do a "ps auxw" on my database server, I see "....idle in transaction". Is this what you meant, and would the steps you talked about with pg_stat_activity help me track down the transactions that are idle? What's strange is that usually this does not cause problems. It is just occasionally that I am unable to gain access to a table. Unfortunately that also makes it hard to track down the source of the problem. Thanks for all your help so far--really appreciate it. Si Michael Fuhr wrote: >On Tue, Feb 01, 2005 at 10:53:11AM -0800, Si Chen wrote: > > > >>I would like to track down what in the application is causing the >>deadlock, >> >> > >Are you sure you understand what "deadlock" means? Deadlock occurs, >for example, when connection A holds a lock that connection B wants >and connection B holds a lock that connection A wants. PostgreSQL >should recognize that situation and cause one of the connections >to fail after a timeout (one second by default). That doesn't sound >like what you're experiencing -- based on what you've said, one >connection holds a lock and another is blocked waiting for it. > > > >>but it's a bit hard since it's a big app with lots going on. >>I can track down the PID of the transaction which is locking the tables, >>but is there anyway to go from the PID back to the SQL statement(s) in >>the transaction? >> >> > >The query "SELECT * FROM pg_stat_activity" should show connections' >current queries if you have stats_command_string set to "on". If >stats_command_string is "off" then you can enable it by editing >postgresql.conf and restarting the postmaster, but unfortunately >that won't help you track down queries that are already running. > >Is it possible that the transaction holding the lock is idle? Some >applications use long-lived transactions that can cause locking >problems in other transactions. > > >
On Tue, Feb 01, 2005 at 02:27:37PM -0800, Si Chen wrote:
> You are right. The transactions are idle--when I do a "ps auxw" on my
> database server, I see "....idle in transaction". Is this what you
> meant, and would the steps you talked about with pg_stat_activity help
> me track down the transactions that are idle?
Here's an example of what you'll get when stats_command_string is "on":
SELECT * FROM pg_stat_activity;
datid | datname | procpid | usesysid | usename | current_query | query_start
-------+---------+---------+----------+---------+---------------------------------------+-------------------------------
17232 | test | 26484 | 100 | mfuhr | <IDLE> in transaction | 2005-02-03 00:20:11.072507-07
17232 | test | 26482 | 100 | mfuhr | <IDLE> | 2005-02-03 00:19:24.445995-07
17232 | test | 26483 | 100 | mfuhr | ALTER TABLE foo ADD COLUMN name text; | 2005-02-03 00:20:36.089689-07
(3 rows)
> What's strange is that usually this does not cause problems. It is just
> occasionally that I am unable to gain access to a table. Unfortunately
> that also makes it hard to track down the source of the problem.
Joining pg_locks and pg_stat_activity can be useful. Here's an example:
SELECT p.procpid, p.usename, p.datname, l.relation::regclass,
l.granted, p.current_query, p.query_start
FROM pg_stat_activity AS p
JOIN pg_locks AS l ON l.pid = p.procpid
WHERE l.relation IN (SELECT relation FROM pg_locks WHERE granted IS FALSE)
ORDER BY l.relation;
procpid | usename | datname | relation | granted | current_query | query_start
---------+---------+---------+----------+---------+---------------------------------------+-------------------------------
26484 | mfuhr | test | foo | t | <IDLE> in transaction | 2005-02-03
00:20:11.072507-07
26483 | mfuhr | test | foo | f | ALTER TABLE foo ADD COLUMN name text; | 2005-02-03
00:20:36.089689-07
(2 rows)
If a connection holding a lock (granted = t) is idle, then you might
want to investigate what it's doing. It might simply have issued
a query, acquiring an AccessShareLock on the table, which conflicts
with the AccessExclusiveLock that ALTER TABLE wants. If that's
the case, then maybe the application could be modified to COMMIT
or ROLLBACK its transactions if it knows it's going to be idle for
a while.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Martha Stewart called it a Good Thing when schen@graciousstyle.com (Si Chen) wrote: > Hello everyone. I am trying to do an ALTER TABLE, but it hangs > indefinitely. I think the table is locked from a transaction, and in > pg_locks I found: > relation 75907 > database 74861 > pid 29604 > mode AccessExclusiveLock > granted f > > Is there a way to release this lock? Or does the database need to > re-started? Note that it says "granted f". The lock has only been requested; it hasn't been granted. Some other transaction is holding onto the table and preventing that lock from being acquired. You might indeed want to rummage around and see what other locks there are on the table, and perhaps interrupt the other processing. This sort of maintenance may require locking out "application users" so that you can get all of the locks you need to upgrade the application. It oughtn't be necessary to shut down the DB, though. -- select 'cbbrowne' || '@' || 'gmail.com'; http://www3.sympatico.ca/cbbrowne/nonrdbms.html "I've seen estimates that 10% of all IDs in the US are phony. At least one-fourth of the president's own family has been known to use phony IDs." -- Bruce Schneier CRYPTO-GRAM, December 15, 2001