Deadlock with one table - PostgreSQL is doing it right
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
Could it be that the tool you are using in Oracle is doing commit while exiting out due to Deadlock, because there is no explicit rollback.
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@gmail.com> wrote:
Show quoted text
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 | 22PostgreSQL: https://youtu.be/rH-inFRMcvQ
Oracle: https://youtu.be/l2IGoaWql64PostgreSQL:
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
2017-12-21 17:46 GMT+01:00 Jeremy Finzel <finzelj@gmail.com>:
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.
Well OK. I made a new one for PostgreSQL: https://youtu.be/En8EFv90yCc
Now with same background color.
2017-12-21 12:41 GMT+01:00 Rakesh Kumar <rakeshkumar464@mail.com>:
Could it be that the tool you are using in Oracle is doing commit while
exiting out due to Deadlock, because there is no explicit rollback.
The tool Im using is "sqlplus". By default you are always in a transaction
and auto-commit only occur on exit.
Please note that Oracle leave the table with a half transaction, i.e. only
one row is updated.
The tool Im using is "sqlplus". By default you are always in a transaction and
auto-commit only occur on exit.
Its been a while since I worked with oracle. I remember I use to add the following two lines at the top:
SET AUTOCOMMIT OFF
whenever SQLERROR EXIT ROLLBACK
IIRC, adding the above ensures that at the first error, oracle will rollback all changes , unless you add a DDL in the transaction which auto commits all changes.
2017-12-21 21:50 GMT+01:00 Rakesh Kumar <rakeshkumar464@mail.com>:
whenever SQLERROR EXIT ROLLBACK
Thanks. You are absolutely right.
After starting with:
WHENEVER SQLERROR EXIT ROLLBACK
the process getting the deadlock will exit to command prompt (with
%ERRORLEVEL% = 0).
So what actually found out was that Oracle has some strange combinations of
default values regarding
1. AUTOCOMMIT = 0
2. Don't exit/rollback on deadlock