Skip to Content

  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.

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. 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

    (0) 
    1. naga sandeep Uppuluri 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

      (0) 
      1. 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

        (0) 
        1. naga sandeep Uppuluri 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.

          (0) 
          1. 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

            (0) 
      2. ashish vikas

        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.

        (0) 
          1. Jose Manuel Salguero

            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

            (0) 

Leave a Reply