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.

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 *