23 August 2006

Sample Deadlock Scenario On Oracle

create table a( x number )
INSERT INTO  a(x) VALUES (10)

create table b( x number )
INSERT INTO b (x) VALUES (100)

commit


--Session1
UPDATE a SET x = 5
--exclusive lock on table a

--Session2
UPDATE b SET x = 1000
--exclusive lock on table b

UPDATE a SET x = 50
--waits Session1 to commit or rollback

--Session1
UPDATE b SET x = 500
--waits Session2 to commit or rollback
--DEADLOCK!!!!

No comments: