Locks, Deadlocks and the like
Deadlock handling
OK, Oracle deals with them and resolves them itself – but they are nasty stuff. And I do not talk about the ORA-00060 in the alert log file, but rather about performance, handling, and the "rejected" transaction. Are deadlocks avoidable? I'd say: At least partially. If Deadlocks are an issue for you, you should investigate your StatsPack / AWR reports:
TM locks without affected rows
These mostly point to Foreign Key constraints without indexes. To solve/prevent these locks, check the affected table for Foreign Keys. If those are not indexed, create indexes for the affected columns.
What's behind it? Easy: Having no index, the locks required for updates need to be managed at block level; so of the same database block contains multiple rows, they are all locked. An index brings a smoother way for locking here.
TX locks with affected rows
Here usually the application is responsible ("application row level conflict"). The task in this case is to ensure rows are always locked in a particular order. If two threads run at the same time, and one locks rows from 1 to n while the other locks from n to one … Well, it's quite obvious they meet somewhere in the middle and create a deadlock.
Further readings
TM Locks: How to identify the locked object?
- check for the resource name in the trace file
- retrieve object id from the TM lock id: TM-AAAAAAAA-BBBBBBBB gives it away with the As marked
- convert object id from HEX to DEC
SELECT * FROM dba_objects WHERE object_id=<dec_id>;
Force DDL to repect Locks
Before Oracle 11g, DDL always respected locks – failing with an ORA-00054
.
This is no longer the case from Oracle 11g onwards, where most DDL operations
can be completed parallel. To indicate DDL statements should respect locks as
they did before, one can:
ALTER SESSION SET ddl_lock_timeout=30;
The value is specified in seconds and indicates how long DDL statements will continually retry to get a lock and execute. If they did not succeed until then, they should fail as before.
It is also possible to make this the DB default:
ALTER SYSTEM SET ddl_lock_timeout=30;