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

Tomasz Zielonka <tomek(at)mult(dot)i(dot)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