Deadlock condition occurs when two or more user is trying to
perform the operation on the same data or in other words transaction override
explicitly so the record is locked by each other.
Suppose we have two sessions and we are trying to update the
same record.
For E.g:-
Session 1:- Suppose we have session-id “101” for session
1.
Query:- Create Table
Emp (Id Number(10),Name Varchar(100));
O/P:- Table Created
Now we are inserting the records into the table
Query:- Insert Into
Emp Values(1,'Jon');
Insert Into Emp Values(2,'Greg');
Commit;
O/P:- 1 row inserted.
1 row inserted.
Commit complete.
Query:- Select * from
Emp;
O/P:-
Id
|
Name
|
1
|
Jon
|
2
|
Greg
|
Condition:- Now we will update the name of the employee
for id 2 but we will not commit this
transaction.
Query:-
Update Emp Set Name=’Ted’ where id=2;
After this we are starting a new session.
Session 2:- Here
we have session-id is “201” for session 2.
In this new session “201” we are will try to update the same
record where id is 2.
Query:-
Update Emp Set Name=’Paul’ where id=2;
This time DeadLock
condition occurs and database enter into the deadlock trap, We need to
execute the below query from third session to check what’s going on? why query
is taking so much time to update the single record in session 2 (i.e session-id “201”).
Query:- SELECT
'Session-Id :'||' '||v1.sid ||' '||'Is Blocking'||' Session-id: '
|| v2.sid as
SessionLockInfo,
Qr.sql_fulltext as QueryLocked
from v$lock
v1, v$lock v2 ,
(select a.sid, a.username,b.sql_id, b.sql_fulltext
from v$session a, v$sql b
where a.sql_id = b.sql_id and a.status = 'ACTIVE' and
a.username != 'SYS') Qr
where v1.block
=1 and v2.request > 0
and v1.id1=v2.id1 and v1.id2=v2.id2 and v2.sid
=Qr.sid;
O/P:-
SessionLockInfo
|
QueryLocked
|
Session-Id : 101 Is Blocking Session-id: 201
|
Update Emp set Name='Paul' Where id=2
|
Deadlock condition automatically clear once the transaction
for session 1(i.e session-id “101”) is completed or canceled, transaction
completed means the user needs to hit the COMMIT or ROLLBACK statement in the session
1(i.e session-id “101”), then the session automatically releases.
What other ideas can you add to this post that I may have not mentioned?
