how to release a transaction lock on a table?

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 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 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 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