Re: session hang for same row update

On Thursday, December 01, 2011 7:10:15 am AI Rumman wrote:
> Could anyone please tell me why the session 2 is hanging?
> I am using Postgresql 9.1.
>
> show deadlock_timeout ;
> deadlock_timeout
> ------------------
> 1s
> (1 row)
>
>
> select * from t2;
> i | nam
> ---+-----
> 2 | t4
> 1 | t3
>
> Session 1:
> BEGIN
> update t2 set nam = 't3' where i=2;
> UPDATE 1
>
> Session 2:
> BEGIN
> update t2 set nam = 't3' where i=2;
> hanged

You did not COMMIT the first transaction.

http://www.postgresql.org/docs/9.1/interactive/transaction-iso.html

"
UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same
as SELECT in terms of searching for target rows: they will only find target rows
that were committed as of the command start time. However, such a target row
might have already been updated (or deleted or locked) by another concurrent
transaction by the time it is found. In this case, the would-be updater will
wait for the first updating transaction to commit or roll back (if it is still in
progress). If the first updater rolls back, then its effects are negated and the
second updater can proceed with updating the originally found row. If the first
updater commits, the second updater will ignore the row if the first updater
deleted it, otherwise it will attempt to apply its operation to the updated
version of the row. The search condition of the command (the WHERE clause) is
re-evaluated to see if the updated version of the row still matches the search
condition. If so, the second updater proceeds with its operation using the
updated version of the row. In the case of SELECT FOR UPDATE and SELECT FOR
SHARE, this means it is the updated version of the row that is locked and
returned to the client

"
--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com