I'm thrilled as it's my first post to asktom.
Here's my remark.
I intentionally provoked a deadlock between two sessions.
Session 1 gets the deadlock error, but still all its actions are not rollbacked and session 2 continues to waits for session 1 to release its lock.
What's the correct coding for the correct handling of the deadlocks? Should we make an exception handler and make a explicit ROLLBACK?
can you explain why deadlocks are not handled the same way as regular errors (why is the rollback not implicit?) and are there any other type of errors that behave as deadlocks do.
here is my test case.procedures definition
CREATE OR REPLACE procedure P_1_1 as
update tbl set col='1' where pk='0H5060V68937';
CREATE OR REPLACE procedure P_1_2 as
update tbl set col='1' where pk='0H5060V66673';
CREATE OR REPLACE procedure P_2_1 as
update tbl set col='2' where pk='0H5060V66673' ;
CREATE OR REPLACE procedure P_2_2 as
update tbl set col='2' where pk ='0H5060V68937';
flow of actions
begin p_1_1; end;
begin p_2_1; end;
begin p_1_2; end;
begin p_2_2; end;
September 14, 2009 - 1:00 pm UTC
... What's the correct coding for the correct handling of the deadlocks? Should we make an exception handler and make a explicit ROLLBACK? ...
You tell me?
What would YOU like to have happen?
I'm not a fan (in fact, I think error handling is something most people do entirely WRONG, ineptly even - and catching exceptions you cannot deal with is a big error, big bug) of coding the exception block - exception blocks should be coded for one of two things:
a) at the top level - the block of code that the client submits - in order to LOG the error, eg: client can submit:
begin p(....); exception when others then log_error( ...); RAISE; end;
and that should be at the TOP LEVEL only - not in every silly routine, just in the bit of code that calls your routine
b) when you EXPECT the error, eg:
select x into y from t where ...;
when no_data_found then y := some_default;
and can recover from it (eg: it is NOT an error).
By default all statements are ATOMIC, the procedure you call either entirely succeeds or entirely fails - UNLESS you
1) put a commit/rollback in there - then game over, the caller of your procedure must deal with the MESS you've left them in - which is an unknown database state (maybe they called 50 other routines before your code - it is UP TO THEM whether they should a) commit, b) rollback, c) retry some operation)
2) put a when others not followed by a raise - then the work that is partially done "stays" and the work never gotten to - is never gotten to. Again, an unknown database state.
The client should receive the error "deadlock detected"
The client should decide whether to a) rollback, b) commit, c) retry operation, d) do something else - only THEY are smart enough to know what is proper in this case.
Your code - should do nothing in this case, you don't know enough at your low level.