Thread: Deadlock
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
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
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
[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