Lock Catalogue

This is a list of all articles I’ve written about locks (or enqueues). Each entry has a date stamp and a short note of the contents. The articles are grouped in a couple of different categories (including one specifically on deadlocks), but otherwise are generally listed most-recent first.

General

• ORA-00054 pt.1 (July 2022): Trouble-shooting with errorstack and ksq trace
  • ORA-00054 pt.2 (July 2022): Trouble-shooting with systemstate trace
• Check Constraints (Jan 2021): an odd difference in locking and waiting between disabling and enabling constraints.
• v$lock (May 2013): queries against v$lock are slow – gathering fixed object stats helps
• Lock Modes (June 2010): my personal reference for translating numeric lock modes.
• Locking SQL (April 2009): there is no explicit path in the v$ views that you can take to get from a locked row to the SQL that locked it

Deadlocks

• Index ITLs (Feb 2022): an extreme demonstration of self-deadlocking deliberately engineered by overuse of autonomous transactions.
• Materialized view oddity (Jan 2018): a strange (possibly transient) deadlock when refreshing nested materialized views.
• Referential Integrity (May 2016): including a discussion of deadlocks due to concurrent processes mixing their inserts of parent and child rows.
• Deadlock trace (Oct 2014): an explanation of how you can find out more details about the source of a deadlock from the deadlock trace file.
• An unusual deadlock (Sept 2014): between a library cache lock and a normal DML (TX) lock. (Includes a link to an example of a deadlock between a rowcache lock and a library cache lock.)
• Shrink Space bug (Mar 2014): a bug, fixed in 12.2, demonstrating that Oracle does not “resolve” deadlocks, it just makes it possible for the application to resolve the deadlock.
• Deadlock Graph (Nov 2013): look carefully at the deadlock graph – it may not be about TX / TM locks.
• Rows waited on (April 2013): be a little cautious about “rows waited on” information following a deadlock graph; the figures may be held over from a previous operation.
• Lock bug (March 2013): an unexpected (and incorrect) deadlock error; possibly due to RAC effects, and probably fixed by late 11.2
• Deadlock Detection (Feb 2013): Oracle does not resolve deadlocks, it merely detects them and makes it possible for the application to resolve them.
• A deadlock variation (Aug 2011): many deadlocks are TX deadlocks in mode 6 (X) – but other modes are possible.
• An IOT deadlock (April 2011): a reason why the deadlock graph may look a little different from the commonest type.