Demonstration
Create some demonstration values:
create table tq84_00600_deadlock (
id integer primary key,
val varchar2(20)
);
begin
insert into tq84_00600_deadlock values (42, 'A');
insert into tq84_00600_deadlock values (99, 'B');
commit;
end;
/
Session 1 updates a record but does not commit the transaction:
update tq84_00600_deadlock set val = 'C' where id = 42;
Session 2 updates another record, also without commiting the transaction:
update tq84_00600_deadlock set val = 'X' where id = 99;
Session 2 tries to update the record which is already updated by another session (i. e. session 1). Because the other session locks the records, session 2 is blocked with the following update statement:
update tq84_00600_deadlock set val = 'Y' where id = 42;
Sesion 1 tries to update the record which is already updated by session 2:
update tq84_00600_deadlock set val = 'D' where id = 99;
This update staement leads to a situation where both would be blocked. Oracle handles this by raising ORA-00060: deadlock detected while waiting for resource in one of the two sessions.
Cleaning up:
drop table tq84_00600_deadlock;