Bill Moseley wrote:
> I seemed to have resolved this issue by a:
>
> LOCK TABLE account IN EXCLUSIVE MODE;
>
> at the very start of the transaction to prevent that transaction
> from running in parallel.
>
> The transaction has quite a few selects and inserts and a few updates.
> I was hoping to not lock at the start of the transaction which will
> effectively serialize that code.
>
> The update that was causing the deadlock was just about the last
> command in the transaction. Removing that update and the deadlocks
> go away.
I think you should be able to do a SELECT FOR UPDATE with the same WHERE
as the UPDATE as the first thing in your transaction. That way it is
much less likely to deadlock with itself. (This assumes that the set of
tuples to update doesn't change, which holds true everytime if your
transaction has isolation level serializable).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.