Re: Deadlock with one table - PostgreSQL is doing it right

It's hard to follow how the 2 videos relate, because you don't run the same
SQL both places. You first update where i = 2 in Postgres and i = 1 in
Oracle.

On Thu, Dec 21, 2017 at 4:37 AM, Hans Schou <hans(dot)schou(at)gmail(dot)com> wrote:

> Hi
>
> FYI - if it has any interest
>
> During my preparation for describing what happens when two processes
> update the same row in a table, I came across that PostgreSQL is doing
> right and Oracle is doing it wrong.
>
> The situation is a process which get a deadlock, but because it is a
> script, it sends a commit anyway. This is bad behavior by humans but that's
> how they are.
>
> After both processes commit's the table should be:
> i | n
> ---+---
> 1 | 11
> 2 | 21
> in Oracle it is:
> i | n
> ---+---
> 1 | 11
> 2 | 22
>
> PostgreSQL: https://youtu.be/rH-inFRMcvQ
> Oracle: https://youtu.be/l2IGoaWql64
>
> PostgreSQL:
> A
> select * from t;
> begin;
> update t set n=n+1 where i=2;
>
> B
> begin;
> update t set n=n+1 where i=1;
> update t set n=n+1 where i=2;
>
> A
> update t set n=n+1 where i=1;
>
> B
> commit;
>
> A
> commit;
>
> best regards
> hans
>