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.
How Deadlock situation occurs?