if not restricted with deadlock counter.
Note: Tested in Oracle 19c
1. Test Setup
First we create a table with 2 rows, and a Plsql code to update them.
drop table test_dl_tab;
create table test_dl_tab as select level id, 0 cnt from dual connect by level <=2;
select * from test_dl_tab;
create or replace procedure test_dl_proc(p_id_1 number, p_id_2 number, p_dl_limit number := 100, p_sleep_init number := 10, p_sleep_dl number := 2) as
e_deadlock_detected exception;
pragma exception_init(e_deadlock_detected, -60);
l_start number := dbms_utility.get_time;
l_end number := dbms_utility.get_time;
l_time_limit number := (p_dl_limit * (3 + p_sleep_dl) + 30);
l_dl_cnt number := 0;
begin
update test_dl_tab set cnt = cnt + 1 where id = p_id_1;
dbms_session.sleep(p_sleep_init);
l_start := dbms_utility.get_time;
l_end := dbms_utility.get_time;
while l_dl_cnt < p_dl_limit and (l_end - l_start)/100 < l_time_limit loop
begin
update test_dl_tab set cnt = cnt + 1 where id = p_id_2;
dbms_session.sleep(p_sleep_dl);
exception when e_deadlock_detected then
l_dl_cnt := l_dl_cnt + 1;
dbms_session.sleep(p_sleep_dl);
end;
l_end := dbms_utility.get_time;
end loop;
-- commit needed
-- Deadlock is a statement level rollback, locked source still locked. Other session is in TX Wait
-- Otherwise "log buffer space" in TX waiting session
commit;
dbms_output.put_line('Deadlock Hits = '||l_dl_cnt||' Within (seconds) = '||((l_end - l_start)/100));
end;
/
2. Test Run
Open two Sqlplus windows (SESSION-1 and SESSION-2), in SESSION-1, we update Row 1, then Row 2 with deadlock counter limited by 20. Whereas in SESSION-2, they are updated in reverse order.
-- Start in SESSION-1
exec test_dl_proc(1, 2, 20);
-- Start in SESSION-2 within 10 seconds of SESSION-1 start
exec test_dl_proc(2, 1, 20);
3. Test Outcome
Here the output from both sessions.
-- SESSION-1
SQL > exec test_dl_proc(1, 2, 20);
Deadlock Hits = 20 Within (seconds) = 103.68
-- SESSION-2
SQL > exec test_dl_proc(2, 1, 20);
Deadlock Hits = 19 Within (seconds) = 131.78
