Log on as SYS or with SYSDBA authority and run this script in all databases. The query depends upon objects that are created by the script $ORACLE_HOME/rdbms/admin/dbmslock.sql. The following script can be used to identify deadlocks in the database. Transaction deadlocks occur when two or more transactions are attempting to access an object with incompatible lock modes.Remote resource requests are requests originating from another instance. The LMD process also handles deadlock detection Global Enqueue Service (GES) requests.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.Add INITRANS - In certain conditions, increasing INITRANS for the target tables and indexes(adding slots to the ITL) can relieve 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.There are several remedies for resolving aborted tasks from deadlocks: Sqlplus>select sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where lockwait is not null) order by address, hash_value, piece *** you need have dba priviledge to kill sessions Sqlplus>alter system kill session 'sid, serial#'
Sqlplus> select sid, serial#, username, command, lockwait, osuser from v$session where lockwait is not nullĢ) To kill a locked session, first need to find sid, serial and use Normally deadlock generates dump file and automatically is released by oracle system process
How to check any deadlock and other locking issues ORA-00060: deadlock detected while waiting for resourceĬause: Transactions deadlocked one another while waiting for resources.Īction: Look at the trace file to see the transactions and resources involved. There's very little you can do about this, apart from not using bitmap indexes in a DML environment.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: If you see the "no row" entries in the Oracle Trace AND the Create table DDL shows bitmap indexes, then this is what is causing the deadlock - this is a prime indicator that this is a bitmap index deadlock.
Resource Name process session holds waits process session holds waits The following information may aid in determining the deadlock: It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. The following deadlock is not an ORACLE error. If the trace shows something similar to this: