Skip site navigation (1) Skip section navigation (2)

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

From: Tomasz Zielonka <tomek(at)mult(dot)i(dot)pl>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: PostgreSQL bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Re: Strange deadlock problem on simple concurrent SELECT/LOCK TABLE transactions
Date: 2001-08-25 08:18:41
Message-ID: 20010825101841.A31824@mult.i.pl (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-08-25 13:57:35
Subject: Re: Re: Strange deadlock problem on simple concurrent SELECT/LOCK TABLE transactions
Previous:From: Peter EisentrautDate: 2001-08-24 22:22:00
Subject: Re: timestamps cannot be created without time zones

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group