... 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:
begin
...
begin
select x into y from t where ...;
exception
when no_data_found then y := some_default;
end;
..........
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.