Skip to Content
Author's profile photo Former Member

How to remove/delete DeadLocks(ORACLE DB)

  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.

Assigned tags

      10 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      You should also add the "immediate" option as well.

      ALTER SYSTEM KILL SESSION '130,620' IMMEDIATE;

      Author's profile photo Former Member
      Former Member

      I think its better to cancel one of the background job with SM37/SM50 in SAP rather than killing oracle session.. what say ?

      Author's profile photo Reagan Benjamin
      Reagan Benjamin

      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

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      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

      Author's profile photo Reagan Benjamin
      Reagan Benjamin

      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

      If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock.  To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock

      Regards

      RB

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      please clarify

      deadlocks are automatically cleared by the database.

                         

                          

      Are deadlocks cleared automatically in all the cases??

      if yes, what is the need for transaction DB01

      Reagan Benjamin wrote:

                             

      Your understanding about deadlock is wrong.

                         

      my understanding about the db deadlock is same as what you have mentioned

      If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock.  To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock

      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.

      http://www.dba-oracle.com/t_deadlock.htm

                         

      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.

      Author's profile photo Reagan Benjamin
      Reagan Benjamin

      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

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hello ashish,

      I was talking about DB01 itself, please dont mix it up with example job scenario and get confused.

      Thanks

      Nag.

      Author's profile photo Former Member
      Former Member

      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