what is a deadlock in oracle?

A deadlock is when two processes are each waiting for the other to release a lock on an object before they will proceed. e.g.

Session A = lock table A
Session B = lock table B
Session A = try to lock table B, need to wait
Session B = try to lock table A, need to wait
*Deadlock!*

Oracle will automatically detect these and kill one of the sessions. A warning will be written to the alert log to indicate what happened.

Hi sathikumar

Just for the sake of clarity :

1) not only a table can be locked in oracle. The deadlocks can occur on several type of resources... For example on rows into the same table :

- user A delete row 1 without commiting
- user B delets row 2 without commiting
- user A wants to delete row 2 (still available because lack of commit from A)
- user B wants to delete row 1 (still available because lack of commit from B)
=> deadlock

2) deadlocks occur because of poor application development. They are not a database problem. The database does exactly what it is supposed to do (it works perfectly). So :
- no need to 'solve' anything (unless give some training to your developers/users)
- oracle will rollback one of the sessions (like the user would have done 'rollback' command). Unfortunately you can not predict which session will be rolled back.

Cheers,
P

In addition to what is said above, following blog will help. (This is about SQL Server, but concepts hold true for Oracle as well)

http://blogs.msdn.com/b/bartd/archive/tags/sql+deadlocks/default.aspx

@jaiminpsoni

SQL Server is not Oracle. Why not reading directly the docs for oracle ?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#sthref2043

P