Deleting Database DeadLocks on ORACLE
Reason
you want to delete database deadlock which is interrupting your work
Note
A deadlock is a situation in which two or more competing actions are each waiting for the other to finish, and thus neither ever finishes.
Example scenario
Backgound job RSLDAPSYNC_USER is scheduled at same time of the day twice.
both the events tries to access the same table USR02 at same instant and waits for the other to finish first
then there are most likely chances for a DB deadlock to be created.
Caution
This must me performed with utmost consious.
procedure
login into the server
login into SQL
And run the following commands,
SQL> select inst_id,sid,serial# from gv$session where username=’username’;
INST_ID SID SERIAL#
————– ——— ———–
1 130 620
SQL> alter system kill session ‘130,620,@1’;
Thus, you have successfully deleted DB Deadlock.
You should also add the “immediate” option as well.
ALTER SYSTEM KILL SESSION ‘130,620’ IMMEDIATE;
I think its better to cancel one of the background job with SM37/SM50 in SAP rather than killing oracle session.. what say ?
You don’t need to kill a session assuming there is a deadlock. When there is a deadlock at the database level one of the SQL statements will fail with an ORA-00060 error and Oracle clears the deadlock. What you are doing is terminating a session. Why ? The SQL statement has already failed.
Regards
RB
Hello Regan,
I think there is an absolute need to go through the example scenario mentioned above.
in the mentioned case, when both jobs runs at the same time, the job owner (ex- ddic) locks the user(x user) in table usr02 and if the deadlock situation occurs, it will never release the user. in that case you cannot modify, delete that user through any transaction and also the user x who is locked will not be able to continue with his work.
and the only way to resolve this would be removing the deadlock
Hi ashish,
canceling one background job after deadlock situation will not help.
Rg,
Nag
Your understanding about deadlock is wrong.
I already mentioned that during a deadlock one statement/transaction will fail and deadlocks are automatically cleared by the database.
What you are doing is killing a session.
Read this for an explanation.
http://www.dba-oracle.com/t_deadlock.htm
Regards
RB
please clarify
Are deadlocks cleared automatically in all the cases??
if yes, what is the need for transaction DB01
my understanding about the db deadlock is same as what you have mentioned
i think you should go through paragraph “note” that i have mentioned in the doc,
moreover i think you should re-refer the link what you have suggested.
in this link , see paragraph
Lock Contention Issues and Solutions
After the database administrator has exhausted possibilities
to visit the design of the database application with the development team, the
next step is to perform further analysis to solve lock contention issues.
Oftentimes, the lock issue is the result of a zombie batch
process or hung database session which has placed an exclusive lock on a
specific row or table, thereby blocking access to the data from other users.
The simple solution to this type of problem is to identify the
particular user and session causing the blocking condition and then to contact
the user so that the session can be killed using the alter system kill session
‘sid,serial#’ immediate command from within SQL*Plus. In the previous section,
numerous locking scenarios and potential solutions were covered.
Good Day
What you are doing is terminating a session just because you think it is a deadlock.
In the event of a deadlock the transaction is rolled back.
Next time when you see a deadlock happened at the DB level check the resulting trace file.
Regards
RB
Sandeep, there is a difference between deadlock in oracle and what you do see in DB01. If Oracle detect a dedlck, it will throw ORA-060 and will kill one session automatically : agreed ?
If you see DB01 lock wait situation for SAP(No Deadlock for Oracle) – you can clear this by job cancellation for sure.
Hello ashish,
I was talking about DB01 itself, please dont mix it up with example job scenario and get confused.
Thanks
Nag.
Hi,
Just to add more information, DB01 is the Oracle Lock Monitor, so all entries there are Oracle Locks but it doesn’t mean that every lock in DB01 is a deadlock.
On the other hand a deadlock is not always because of an application issue, there are some cases regarding INITRANS and MAXTRANS that can also produce deadlocks and ORA-00060 errors.
Th KBA 1872040 – Handling ORA-00060 error – deadlock issue has useful information.
Cheers.
Regards