Re: Deadlock

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