Understanding Locks and Enqueues

1. Understanding Locks and Enqueues How to Approach Common Blocking Lock Scenarios By Mark J. Bobak November 20th, 2008

2. Introduction • Senior Oracle DBA at ProQuest Company • Working with Oracle since version 6 • Present regularly at the local, national and international level • Member of the OakTable Network since 2001

3. Disclaimer • At least one statement in this presentation is incorrect, but it may be this one.

4. Agenda • What is an Enqueue? • Difference between locks, enqueues, and latches • Characteristics of Enqueues • Most Common Types of Enqueues • TM – Table Modification Enqueue • TX – Transaction Enqueue • Wait Scenarios of TM and TX enqueues • Anatomy of a row-level Lock

5. What is an Enqueue? • An Enqueue protects shared resources from concurrent, incompatible accesses • Prevent two sessions from writing the same row of a table at the same time • Facilitates enforcement of parent/child locking for referential integrity • Prevent two sessions from updating the definition of a table (drop/add column, etc) at the same time

6. What is an Enqueue? (p.2) • Enqueues differ from latches: • Latches are the Database’s version of a mutex • Whereas latches (mostly) provide for mutually exclusive access, enqueues allow for shared access, if mode is compatible • Enqueues allow for enqueueing, that is, sessions waiting for access to an enqueue will queue in a line • In the case of latches, sessions waiting for access must spin or sleep, and there’s no guarantee who will get the latch next

7. What is an Enqueue? (p.3) • An Enqueue has three lists: • Holder: List of sessions that currently hold the enqueue, and in what mode • Waiter: List of sessions that are waiting to acquire the enqueue in a mode incompatible with what’s being held • Converter: List of sessions that are currently holding the enqueue in one mode, and waiting to convert their lock to a more restrictive mode, incompatible with a mode already held by some other session

8. Ok, but what’s an Enqueue, really?? • An enqueue is an element in an array of enqueues that are allocated at SGA creation time. (Visible in X$KSQEQ, number of elements controlled by _enqueue_locks) • It’s a state object, like so many other components in the SGA. (process state objects, session state objects, enqueue state objects, etc.)

9. Most Common Enqueue Types • List of most common enqueues • CF – Controlfile • DM – Database Mount • MR – Media Recovery • RS – ?? (not documented in list of enqueue names for 10g or 11g) • RT – Redo Thread • TM – DML • TO – Temporary Table Object • TS – Temporary Segment or Tablespace • TX – Transaction • UL – User Lock

10. Most Common Enqueues • Though there are many common enqueues, usually only TM and TX create problems for applications and developers. • TM – DML, or table enqueue • TX – Transaction enqueue

11. TM Enqueue • The TM or DML enqueue is taken at the table level, by any session doing DDL or DML. • DDL takes TM enqueue in ‘X’ (exclusive) mode, which prevents other sessions doing any DML or DDL while the table definition is changing • DML takes TM enqueue in ‘S’ (shared) mode, which allows for concurrent DML on the same table, but locks out DDL.

12. TM Enqueue (p. 2) • Is used when foreign key relationships are enforced • Locking strategy is much more aggressive in the case when indexes on the child table’s foreign key columns are missing. • Missing indexes on child table foreign key columns is a common cause of waits and deadlocks.

13. TM Enqueue (p.3) • One exception is direct load insert. In the case of direct load insert, a session will take the TM enqueue in ‘X’ mode. This prevents any other DML from taking place while the direct load is happening, in addition to blocking all DDL. • TM enqueues may be disabled • At the instance level, via dml_locks=0 (If RAC, all instances must have same value of dml_locks) • On a per table basis, with ‘alter table … disable locks;’

14. TM Enqueue (p. 4) • There are two different wait scenarios you’ll likely encounter with the TM enqueue. • ‘X’ mode waiting on ‘X’ mode being held. • This is likely to be a session holding ‘X’ mode during direct load insert, while another session attempts to initiate a direct load into the same table. • ‘SSX’ mode waiting on ‘SX’ mode being held. • This is due to unindexed foreign keys

15. TX Enqueue • The TX or Transaction Enqueue is at the heart of Oracle’s row locking mechanism. • The TX enqueue points to an undo segment header, slot within that header, and wrap number. • Before images of any changes are stored in the body of that undo segment, and point to that segment header/slot/wrap.

16. TX Enqueue (p. 2) • When a transaction commits, the only action that’s guaranteed to happen is that the undo segment slot is marked committed, and the TX enqueue is released. • Many different wait scenarios may be observed, each with a different root cause.

17. TX Enqueue (p. 3) • In every (known) case, the TX enqueue will be held in ‘X’ mode. • If a session is waiting to acquire a TX enqueue in ‘X’ mode, it’s simple row-level locking or PK/UK enforement on an existing row. • If a session is waiting to acquire TX enqueue in ‘S’ mode, there are several possibilities. • ITL slot shortage

18. TX Enqueue (p.4) • If a session is waiting to acquire TX enqueue in ‘S’ mode, there are several possibilities. (cont.) • Bitmap index locking • Is the segment an IOT? If so, it’s simple row-level locking • On insert of duplicate, uncommitted PK or UK • Very rarely, freelist contention (no longer relevant if you’re using ASSM)

19. Monitoring Enqueues • V$LOCK • List of current enqueues, as well as sessions holding and waiting on enqueues • Shows TYPE of enqueue, mode held, mode waited, as well as ID1 and ID2 that help interpret information about the enqueue • V$SESSION_WAIT • Can be used to quickly identify waiting session • V$SESSION • Starting with 10g, V$SESSION has extended information, including: • Wait information from V$SESSION_WAIT • And especially, BLOCKING_INSTANCE and BLOCKING_SESSION

20. Monitoring Enqueues (p. 2) • If you’re still not on at least 10g, there are couple of other options • catblock.sql, available in ?/rdbms/admin/catblock.sql will create DBA_BLOCKERS, DBA_WAITERS, etc • As of 10g, still distributed, but, appears to be out of date? • Steve Adams’ locking scripts, available on his website, http://www.ixora.com.au/

21. Monitoring Enqueues (p. 3) • Looking at V$LOCK, the TYPE column identifies the type of enqueue, i.e., TM, TX, UL, etc • ID1 and ID2 may carry additional information, but are context-sensitive with respect to the enqueue TYPE • For TM enqueue, ID1 is the OBJECT_ID of the object being locked, which can be referenced in DBA_OBJECTS, and ID2 is always 0 • For TX Enqueue, ID1 and ID2 hold the undo segment number, slot number, and wrap.

22. Monitoring Enqueues (p. 4) • For TX enqueue, ID1 and ID2 must be decoded using this formula: • trunc(id1/power(2,16)) USN • bitand(id1,to_number('ffff','xxxx'))+0 SLOT • id2 SEQ/WRAP

23. Row-level Locking • The first DML in a session where a transaction does not already exist will implicitly create a transaction. • An undo segment number, slot, and wrap will be assigned • TX enqueue (state object) will be instantiated • When a row to be modified is identified, session will take an entry in the ITL of the data block, assign it to the transaction • USN/SLOT/WRAP will be written to ITL slot, reserving that slot for the current transaction • Lock will be taken on the row, by setting the lock byte in the row directory to point to the current transaction’s ITL slot

24. Demo Time! • Row locking demo • If time, do more demos

25. Questions? • Comments? • Criticisms?

26. More Information • Oracle Documentation • http://tahiti.oracle.com/ • Oracle MetaLink • http://metalink.oracle.com/ • OakTable Website • http://oaktable.net/ • Jonathan Lewis • http://jonathanlewis.wordpress.com/ • http://www.jlcomp.demon.co.uk/ • Steve Adam’s Website • http://www.ixora.com.au/ • Tom Kyte’sAskTom • http://asktom.oracle.com/