Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

 

Oracle deadlocks tips


Oracle Tips by Burleson Consulting

 


What is an Oracle deadlock?

Whenever you have competing DML running against the same data, you run the risk of a deadlock.  This deadlock condition is an age-old issue known as the "perpetual embrace"!  The doc note that a retry may work:

ORA-00060: deadlock detected while waiting for resource

Cause: Transactions deadlocked one another while waiting for resources.

Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.

Deadlocks in Oracle result in this error:

ORA-00060: deadlock detected while waiting for resource

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.

See my notes here on resolving the deadlock detected error.

Resolving Oracle deadlocks

There are several remedies for resolving aborted tasks from deadlocks:

  • Tune the application - Single-threading related updates and other application changes can often remove deadlocks.  Re-scheduling batch update jobs to low-update times an also help.
     
  • Add INITRANS - In certain conditions, increasing INITRANS for the target tables and indexes(adding slots to the ITL) can relieve deadlocks.
     
  • Use smaller blocks with less data - Since the deadlock contention is at the block-level, consider moving these tables and indexes to a super-small blocksize (create a db2k_cache_size), and using a high PCTFREE to space-out the data over MORE blocks.

Inside Oracle deadlock machinations


The LMD process also handles deadlock detection Global Enqueue Service (GES) requests. Remote resource requests are requests originating from another instance.

Deadlock Detection

Transaction deadlocks occur when two or more transactions are attempting to access an object with incompatible lock modes. The following script can be used to identify deadlocks in the database. The query depends upon objects that are created by the script $ORACLE_HOME/rdbms/admin/dbmslock.sql. Log on as SYS or with SYSDBA authority and run this script in all databases. You may have to run the deadlock monitoring script below numerous times before you identify the transaction that is causing the problem.

The Enqueue Deadlock Per Sec Oracle metric is the number of times per second that a process detected a potential deadlock when exchanging two buffers and raised an internal, restartable error.

Avoiding Deadlock Conditions

A deadlock can occur whenever multiple users are in a waiting pattern for data locked by each other. Deadlocks prevent some transactions from continuing to work. In the event of deadlock, Oracle writes the message and error in the form of an ORA-60 error to the Oracle alert.log file. The following diagram illustrates the perfect storm condition that causes a deadlock or deadly embrace to occur within Oracle .
 
Deadlock problems have a similar root cause as that found with basic locking issues with Oracle which is the result of poor database application design. To resolve deadlock conditions with Oracle, the DBA needs to work together with the developer and software engineering team to modify or rewrite the database application code so that such deadlocks do not reoccur.

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.

See my related notes on deadlocks here:


 


 

 

 

 
 
 
 
 

 
 
 
 
 
 
Oracle training Excel
 
Oracle performance tuning software 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright 1996 -  2014

All rights reserved by Burleson

Oracle is the registered trademark of Oracle Corporation.