Deadlocks in SQL

For a transactional database , Deadlocks in SQL may be defined as a situation in which two or more oracle sessions are competing against the same data and waiting for each other to release the mutually locked resources.

Illustration :

User 1:

Suppose User 1 performs a delete operation on Emp table of Scott user for emp 10. But does not commit.

DELETE FROM SCOTT.EMP WHERE EMPNO = 10;

——– 1 row deleted

User 2:

User 2 performs an update operation on Emp table of Scott user for emp 10.

UPDATE SCOTT SET SAL = 20000 WHERE EMPNO = 10;

As User 1 did not perform commit , therefore user 2 can still see the record for emp 10 although this record has been deleted by user 1. The User 2 will go in wait condition since the record for emp 10 is locked by User 1. User 2 cannot perform the transaction unless User 1 releases the lock.

Now suppose User 2 performs a delete operation on Emp table of Scott user for emp 11. But does not commit.

DELETE FROM SCOTT.EMP WHERE EMPNO = 11;

———– 1 row deleted

User 1 :

Now suppose User 1 performs an update operation on Emp table of Scott user for emp 11.

UPDATE SCOTT SET SAL = 30000 WHERE EMPNO = 11;

As User 2 did not perform commit , therefore User 1 can still see the record for emp 11 although this record has been deleted by user 2. The User 1 will go in wait condition since the record for emp 11 is locked by User 2. User 1 cannot perform the transaction unless User 2 releases the lock.

Thus User 1 is dependent on User 2 and User 2 is dependent on User 1. Both are mutually waiting for each other to release the locks. This situation is called Deadlock State.