We regularly get complains from application user that database connection is very slow.
There could be many reasons for this but most of the time it is due to deadlock.
So here I would be explaining what is deadlock and how to resolve it.
The Application Deadlock is not reported in to Database Alert log files, Which makes this more difficult to resolve. The approach used in this post is general method for resolving slow database session So, A DBA can also use this approach for other Slow User session performance issues.
What is Deadlock ?
Suppose there are two friends A and B. A has an apple and want a mango and B has a mango and wants apply and none of them is ready to leave what they have and want what other have. Now, this is called as Deadlock. Same happens in case of Database Sessions, One session holds lock on an row and want to another one which is hold by second one and both need others rows which cause a deadlock.
Steps to Reproduce Deadlock:
I opened two database session from same database user scott and update same row in both sessions without commit and generate a dead lock situation
from session 1:
from session 2:
Above update statement is hang and not responding has generated a Deadlock. DBA cross check alert log file of database which is not showing any error message like 'ORA-0060 Deadlock Detected'
1. Find Deadlock Session:
Here from BLOCKING_INSTANCE and BLOCKING_SESSION values, this is clear that sid 136 (Session #2) is blocked by an Instance number 1 and session number 54. let's find out in what both blocking and blocked session are executing.
2. Blocked Session Detail:
Using below command find what query is getting executed by sid 47 (get SQL_ID from above commnd).
Database Administrator finds This session (Blocked Session) is executing an update statement on dept table.
3. Blocking session detail:
Let' find what blocking session is doing using below command
Final Solution:
DBA has to ask either kill the blocked session or ask user to either commit or terminate his session. I execute commit in session 1 and it automatically release locks and session 2 also got his row updated.
There could be many reasons for this but most of the time it is due to deadlock.
So here I would be explaining what is deadlock and how to resolve it.
The Application Deadlock is not reported in to Database Alert log files, Which makes this more difficult to resolve. The approach used in this post is general method for resolving slow database session So, A DBA can also use this approach for other Slow User session performance issues.
What is Deadlock ?
Suppose there are two friends A and B. A has an apple and want a mango and B has a mango and wants apply and none of them is ready to leave what they have and want what other have. Now, this is called as Deadlock. Same happens in case of Database Sessions, One session holds lock on an row and want to another one which is hold by second one and both need others rows which cause a deadlock.
Steps to Reproduce Deadlock:
I opened two database session from same database user scott and update same row in both sessions without commit and generate a dead lock situation
from session 1:
from session 2:
Above update statement is hang and not responding has generated a Deadlock. DBA cross check alert log file of database which is not showing any error message like 'ORA-0060 Deadlock Detected'
1. Find Deadlock Session:
Here from BLOCKING_INSTANCE and BLOCKING_SESSION values, this is clear that sid 136 (Session #2) is blocked by an Instance number 1 and session number 54. let's find out in what both blocking and blocked session are executing.
2. Blocked Session Detail:
Using below command find what query is getting executed by sid 47 (get SQL_ID from above commnd).
Database Administrator finds This session (Blocked Session) is executing an update statement on dept table.
3. Blocking session detail:
Let' find what blocking session is doing using below command
Final Solution:
DBA has to ask either kill the blocked session or ask user to either commit or terminate his session. I execute commit in session 1 and it automatically release locks and session 2 also got his row updated.
No comments:
Post a Comment