DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Help needed with Deadlock problem

  1. #1
    Join Date
    Jul 2000
    Posts
    6
    Hello all,

    I got a deadlock while testing a procedure to update a table on our data warehouse. This was a long updating batch file. Yesterday I ran this procedure and it was too slow and I killed the process using instance manager without checking the status on it, today, I ran it again and killed it using instance manager. Then, I tried to kill the sessions from SQL*PLUS using 'alter system kill session 'sid, serial#';' I got a message 'ORA-000031: the process was marked to kill'. Now, we are shutting down the database with 'shutdown immediate' command from server manager, hoping to solve the problem. My question is: how could we do to get rid of the deadlock? Shutting it down is an option? Your advice would be highly appreciated. Thanks in advance.

    Li

  2. #2
    Join Date
    Nov 2000
    Location
    Birmingham, UK
    Posts
    360
    I've recently had a similar problem. We had a query running for 3 days(!!!) in our UAT environment that was only noticed because we tried to run in a package and a deadlock occured.
    I killed the session through top session monitor (running 7.3.4) and the process was marked for killing. However the process kept churning away. We weren't sure whether it was rolling back or waiting for PMON to kill it and release the resources. In the end we tries a shutdown immediate but that was taking to long so we went for a shutdown abort and re-start.

    I've had many problems with killing a process through the GUI tools - PMON seems to take an age to wake up and kill the process. I have found it better to kill the session through the O/S (we are running AIX) which immediately releases the resources.

    Hope this helps

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width