Monday, August 4, 2008

How Deadlock occurs?

Dealdlock

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.

Example:
create table sample(id number,Country varchar2(10) );

insert into sample values ( 1, 'China' );
insert into sample values ( 2, 'France' );
commit;

SQL> Select * from sample ;
id country
1 China
2 France


Issue the following command in session1:
SQL> update sample set country='Italy' where id=1;

Issue the following command in Session 2:
SQL> update sample set country='Canada' where id=2;
SQL> update sample set country='Canada' where id=1;

Session2 is now waiting for the lock held by Session1

Issue the following command in session1:
SQL> update sample set country='Italy' where id=2;

This update would cause Session1 to wait on the lock held by Session2, but Session2 is already waiting on this session. This causes a deadlock

No comments: