Forum OpenACS Development: Deadlock detected (in Postgres 7.3x)

Posted by Jade Rubick on 03/30/04 01:44 AM

I have had this happen on a couple of occasions, and wondered if any of you had any suggestions.

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

SQL:
select logger_entry__new ('......')
while executing
...

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?

2: Re: Deadlock detected (in Postgres 7.3x) (response to 1)

Posted by Andrew Piskorski on 04/05/04 03:57 AM

To fix it your probably going to have to either track down exactly what's causing that deadlock, or just keep re-running things until you get all your data in and the upgrade is done.

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.

3: Re: Deadlock detected (in Postgres 7.3x) (response to 2)

Posted by Jade Rubick on 04/05/04 08:13 PM

That's what seems so weird to me. I can't think of any way an upgrade script with nothing else running could deadlock itself. Especially since nothing is done in parallel.

The only thing I can think of that could happen during the upgrade is database vacuuming. Has anyone heard of that causing problems?

4: Re: Deadlock detected (in Postgres 7.3x) (response to 1)

Posted by Janine Ohmer on 04/05/04 10:37 PM

An off-the-wall suggestion....

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?