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

[Jeremy Finzel <finzelj@xxxxxxxxx>]

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.schou@xxxxxxxxx> 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