By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 818513 fiddles created (9189 in the last week).
create table DEMO(id int primary key, a int not null, b int);
✓
hidden batch(es)
insert into DEMO values(1,1,1);
1 rows affected
hidden batch(es)
insert into DEMO values(2,2,2);
1 rows affected
hidden batch(es)
commit;
✓
hidden batch(es)
update DEMO set b=b+1 where a=1;
1 rows affected
hidden batch(es)
declare
pragma autonomous_transaction;
r sys_refcursor;
begin
-- read access the row that is not locked
open r for select * from DEMO where a=2;
dbms_sql.return_result(r);
-- read access the row that is locked
open r for select * from DEMO where a=1;
dbms_sql.return_result(r);
-- write access on the row that is not locked
delete from DEMO where a=2;
-- write access on the row that is locked
delete from DEMO where a=1;
commit;
end;
/
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 14
