Thread: Deadlock with one table - PostgreSQL is doing it right
Hi
FYI - if it has any interestAfter both processes commit's the table should be:
i | n
---+---
1 | 11
2 | 21
in Oracle it is:
i | n
---+---
1 | 11
2 | 22
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;
Could it be that the tool you are using in Oracle is doing commit while exiting out due to Deadlock, because there is noexplicit 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:
hansbest regardsOracle: https://youtu.be/l2IGoaWql64The 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.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.HiFYI - if it has any interestAfter both processes commit's the table should be:i | n---+---1 | 112 | 21in Oracle it is:PostgreSQL: https://youtu.be/rH-inFRMcvQi | n---+---1 | 112 | 22PostgreSQL:
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;
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 transactionwhich 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