Oracle Scratchpad

February 19, 2022

Lock Catalogue

Filed under: Oracle — Jonathan Lewis @ 10:34 am GMT Feb 19,2022

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

  • 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.

1 Comment »

  1. […] Locking catalogue […]

    Pingback by Catalog Catalogue | Oracle Scratchpad — February 19, 2022 @ 10:35 am GMT Feb 19,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: