Thread: Deadlock

Deadlock

From
Bart McFarling
Date:
Is there some kind of log, table or something that I could get more
information about a deadlock situation that is occurring in my database? I
just get a transaction number and a process id, which is useless to me
because my application terminates on any errors from the database? It occurs
infrequently (about once a day) and I have no idea how to track it down.
Also sometimes the database just freezes (RedHat EL 3.0 Postgresql 8.0.1)
could this be a deadlock situation? There is nothing in the log about
deadlock or anything else when it freezes (this happens about once every
month or so)

Thanks,
Bart


Re: Deadlock

From
Michael Fuhr
Date:
On Thu, Sep 15, 2005 at 12:32:05PM -0500, Bart McFarling wrote:
> Is there some kind of log, table or something that I could get more
> information about a deadlock situation that is occurring in my database? I
> just get a transaction number and a process id, which is useless to me
> because my application terminates on any errors from the database?

See "Error Reporting and Logging" in the "Server Run-time Environment"
chapter of the documentation:

http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-CONFIG-LOGGING

> It occurs infrequently (about once a day) and I have no idea how to
> track it down.

You could log all queries or use log_min_error_statement to log
only queries that result in an error.  Typical causes of deadlock
are multiple transactions updating the same records in different
orders, and doing inserts/updates that reference the same foreign
keys in different orders (in released versions of PostgreSQL,
referential integrity checks do a SELECT FOR UPDATE on the referenced
key to ensure that it doesn't change while the transaction is still
active; in 8.1 such locks will be acquired with SELECT FOR SHARE,
which should reduce the incidence of deadlock).

> Also sometimes the database just freezes (RedHat EL 3.0 Postgresql 8.0.1)
> could this be a deadlock situation? There is nothing in the log about
> deadlock or anything else when it freezes (this happens about once every
> month or so)

What are the symptoms of this "freeze"?  Do only some queries block?
Do all queries block, even queries such as "SELECT now()"?  Are you
able to connect to the database at all?  If you can connect, have
you examined pg_locks?  If you can't connect, have you done a process
trace or used a debugger to see what the database is doing?

--
Michael Fuhr

Re: Deadlock

From
Bart McFarling
Date:



On Thu, Sep 15, 2005 at 12:32:05PM -0500, Bart McFarling wrote:
> Is there some kind of log, table or something that I could get more
> information about a deadlock situation that is occurring in my database? I
> just get a transaction number and a process id, which is useless to me
> because my application terminates on any errors from the database?

See "Error Reporting and Logging" in the "Server Run-time Environment"
chapter of the documentation:

http://www.postgresql.org/docs/8.0/interactive/runtime-config.html#RUNTIME-C
ONFIG-LOGGING

Will do. Ive played with these settings before, If I knew what tables the
processes were simultaneously trying to update, I think I could fix it
pretty quick. I know which application is locking it, I just cant figure out
why or where, this particular program is a beast its updating at least 10
tables with the push of 1 button. The funny thing is that the people who use
the system are prone to do each others work at the same time because they
don't know that the other has done it. But the deadlocks typically occur
between people in other locations (Dallas bumps heads with Memphis) which is
weird because they shouldn't even be looking at each others data. Im about
to replace the offending software so hopefully the re-written version wont
have this problem but a lot of the code was salvaged for use in the new
version so it may still suffer.  I use the user_write_lock_oid()(or
something like that) to keep people from accessing the same record.

> It occurs infrequently (about once a day) and I have no idea how to
> track it down.

You could log all queries or use log_min_error_statement to log
only queries that result in an error.  Typical causes of deadlock
are multiple transactions updating the same records in different
orders, and doing inserts/updates that reference the same foreign
keys in different orders (in released versions of PostgreSQL,
referential integrity checks do a SELECT FOR UPDATE on the referenced
key to ensure that it doesn't change while the transaction is still
active; in 8.1 such locks will be acquired with SELECT FOR SHARE,
which should reduce the incidence of deadlock).

> Also sometimes the database just freezes (RedHat EL 3.0 Postgresql 8.0.1)
> could this be a deadlock situation? There is nothing in the log about
> deadlock or anything else when it freezes (this happens about once every
> month or so)

What are the symptoms of this "freeze"?  Do only some queries block?
Do all queries block, even queries such as "SELECT now()"?  Are you
able to connect to the database at all?  If you can connect, have
you examined pg_locks?  If you can't connect, have you done a process
trace or used a debugger to see what the database is doing?

I can psql in a get a prompt but any statement will just freeze, regardless
of the table.

Im not sure if a SELECT now() will freeze. I wish I had gotten this email
earlier it hung about 5 minutes before I received this. Yes ive tried
looking at PQtrace() data but there are so many connections/transactions
going on its hard to tell what's what.

--
Michael Fuhr

Re: Deadlock

From
Michael Fuhr
Date:
[Please be careful with quoting -- you quoted some parts of my reply
but not others so it looks like you wrote those sections.  I've
fixed that in this reply.]

On Thu, Sep 15, 2005 at 03:20:02PM -0500, Bart McFarling wrote:
> > > Also sometimes the database just freezes (RedHat EL 3.0 Postgresql 8.0.1)
> > > could this be a deadlock situation? There is nothing in the log about
> > > deadlock or anything else when it freezes (this happens about once every
> > > month or so)
> >
> > What are the symptoms of this "freeze"?  Do only some queries block?
> > Do all queries block, even queries such as "SELECT now()"?  Are you
> > able to connect to the database at all?  If you can connect, have
> > you examined pg_locks?  If you can't connect, have you done a process
> > trace or used a debugger to see what the database is doing?
>
> I can psql in a get a prompt but any statement will just freeze, regardless
> of the table.

What kinds of statements did you try?  Simple SELECTs?  Try querying
some other table, like pg_class or a test table that you created
just for that purpose.  If those queries work then the problem is
probably with locking.  You mentioned that you were using
user_write_lock_oid() but I don't think that should acquire a strong
enough lock to block all other queries to a table.  Are you doing
anything with LOCK?  What about database maintenance activities
like VACUUM FULL or CLUSTER?

The next time the "freeze" happens, try running the following query:

SELECT relation::regclass, * FROM pg_locks;

If you have have stats_command_string enabled then it might also be
useful to run this query:

SELECT * FROM pg_stat_activity;

Look for locks that haven't been granted, then look for the process
that holds locks on that table and see what that process is doing.
If that doesn't help track down the problem then you might need to
log every statement so you can see exactly who is doing what.

--
Michael Fuhr