Forum OpenACS Development: Deadlock detected (in Postgres 7.3x)
I'm doing a bulk upgrade of an ACS website to OpenACS. On two different import scripts, I have received errors like this:
ERROR: deadlock detected
select logger_entry__new ('......')
This has happen when importing logger entries, and now in my own package. In both cases, it was within a Tcl proc (logger::entry::new and my_package::new) that calls a plsql function.
It always happens in different places, and sometimes doesn't happen at all. No other users are using the system, so I'm not sure why deadlock would occur.
Any easy fix to this?
Are you by chance updating tables a,b,c in one transaction, and tables b,c,a in another transaction - same tables but different order? That's one easy way I know of to generate deadlocks in Oracle, and annoying because AFAIK there are no tools whatsoever to help you avoid it, either.
But, this is during a site upgrade, so presumably you have only one single transaction going at a time, right? Offhand, I can't think of any way that a single transaction could possibly deadlock itslef, in fact I believe that's impossible. So something seems weird there...
Oh, btw, I'm thinking in terms of Oracle 8.x here. PostgreSQL should be basically the same (both use MVCC, etc.) but I'm sure there must be differences in the details of the two RDBMSs' deadlock detection algorithms.
The only thing I can think of that could happen during the upgrade is database vacuuming. Has anyone heard of that causing problems?
I recently had a situation where an error occurred within db_transaction, and was handled via some custom error reporting code instead of the usual catch mechanism. The error got reported but the database handle never got freed, so other threads started stacking up. It wasn't a deadlock per se, but it was conceptually similar.
I'm wondering if you might be having something similar happening in your script?