Re: Re: Strange deadlock problem on simple concurrent SELECT/LOCK TABLE transactions

On Fri, Aug 24, 2001 at 04:00:59PM -0400, Tom Lane wrote:
> Tomasz Zielonka <tomek(at)mult(dot)i(dot)pl> writes:
> > It DOESN'T happen in PostgreSQL 7.1beta3
> > It DOES happen in PostgreSQL 7.1beta4 (but on a different machine)
>
> You shouldn't be using *any* beta version anymore. However,

I know, it's not for production use. Normally I use 7.1.2 and 7.1.3, but
I wanted to compare with previous versions.

It seemed strange to me, that 7.1beta3 doesn't run into deadlock here.

> 'begin; select * from items; lock table items; commit;'
>
> is deadlock-prone coding: you are first acquiring a read lock on the
> items table, and then trying to upgrade to an exclusive lock. The
> fact that you get deadlocks is not a Postgres bug.

Oh, now I see. They both have read locks, and both want to extend them
to exclusive locks. Hmmm... right.

************

I still don't understand, why this lock is so persistent. Even when both
servers finished and I started a new session, I couldn't SELECT anything
from this table.

Maybe that's because I interrupted psql with Ctrl-C?

************

So what would be a safe way to do this?
We have an application, where database updates must be done in the safest way
possible.

Is using LOCK TABLE good idea?
SERIALIZABLE doesn't work for us.
But again, maybe we are doing something wrong ;)

Readonly queries are not performed in BEGIN...COMMIT blocks. Only sequences of
queries which contain UPDATEs and INSERTs are in transactions. It didn't work,
so w added a sequence of LOCK TABLEs (which are always performed in the same
order) and now we get this deadlock.

Maybe every query, even readonly, should be in
BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
without locks?
Could it help?

Thanks for your help

tom

--
.signature: Too many levels of symbolic links