Deadlocks in SQL

Deadlock

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.

Print Friendly, PDF & Email
Transaction Control Language (TCL Commands)
Logical Operators
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *