|
How Deadlock situation occurs?
Deadlock is a situation that occurs when two or more users are waiting for data locked by each other. Oracle automatically detects a deadlock and resolves them by rolling back one of the statements involved in the deadlock. The example below demonstrates how deadlock occurs.
Suppose there is a table test with two rows.
create table test (
row_row_num row_number,
txt varchar2(10) );
insert into test values ( 1, 'First' );
insert into test values ( 2, 'Second' );
commit;
SQL> Select * from test ;
ROW_NUM TXT
1 First
2 Second
Ses#1: Issue the following command:
SQL> update test set txt='ses1' where row_num=1;
Ses#2: Issue the following command:
SQL> update test set txt='ses2' where row_num=2;
SQL> update test set txt='ses2' where row_num=1;
Ses#2 is now waiting for the lock held by Ses#1
Ses#1: Issue the following command:
SQL> update test set txt='ses1' where row_num=2;
This update would cause Ses#1 to wait on the lock held by Ses#2, but Ses#2 is already waiting on this session. This causes a deadlock
| 
