Strange deadlock problem on simple concurrent SELECT/LOCK TABLE transactions

Hi!

I forgot to add:

It DOESN'T happen in PostgreSQL 7.1beta3
It DOES happen in PostgreSQL 7.1beta4 (but on a different machine)

Greetings,
tom

--
.signature: Too many levels of symbolic links

Tomasz Zielonka <tomek@mult.i.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,

'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.

regards, tom lane

On Fri, Aug 24, 2001 at 04:00:59PM -0400, Tom Lane wrote:

Tomasz Zielonka <tomek@mult.i.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

Tomasz Zielonka <tomek@mult.i.pl> writes:

Is using LOCK TABLE good idea?

Only if you do it before any other access to the tables to be locked.

SERIALIZABLE doesn't work for us.
But again, maybe we are doing something wrong ;)

Maybe. To use serializable mode you have to be prepared to back off and
retry the whole transaction (on the client side) when you get a "can't
serialize" failure. But except for that retry loop, it's a very clean
programming model.

Readonly queries are not performed in BEGIN...COMMIT blocks. Only sequences of
queries which contain UPDATEs and INSERTs are in transactions.

That should be okay, as long as you remember that two successive
readonly queries won't necessarily see the same state of the database.

When you read data, do calculations with it, and write back the results
of those calculations, you want the reads and writes to be all within
one transaction --- especially if you're using serializable mode; the
consistency guarantees of serializable mode don't hold otherwise.

regards, tom lane