Chapter 15 lock contention
1 / 12

Chapter 15 Lock Contention - PowerPoint PPT Presentation

  • Uploaded on

Chapter 15 Lock Contention. Lock Types and Modes. Lock types specified with two characters Can be found in V$LOCK_TYPE Nearly 200 lock types Most common are TM (DML) TX (transaction) Locks can be seen in V$LOCK. Lock Types and Modes (cont.). Lock modes Shared row Exclusive row

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Chapter 15 Lock Contention' - percy

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Chapter 15 lock contention

Chapter 15Lock Contention

Lock types and modes
Lock Types and Modes

  • Lock types specified with two characters

    • Can be found in V$LOCK_TYPE

    • Nearly 200 lock types

    • Most common are

      • TM (DML)

      • TX (transaction)

  • Locks can be seen in V$LOCK

Lock types and modes cont
Lock Types and Modes (cont.)

  • Lock modes

    • Shared row

    • Exclusive row

    • Shared table

    • Shared row exclusive

    • Table

    • See table 15-1 on p. 461

Waiting for locks
Waiting for Locks

  • By default, locks are released by:

    • COMMIT


  • Waiting for locks degrade performance

  • Sessions can choose whether to wait for lock

    • NOWAIT clause

    • WAIT n clause (where n = number of seconds)


  • Use FOR UPDATE to lock rows to be updated

  • Deadlocks occur:

    • When two transactions are waiting for the other

    • Oracle eliminates deadlocks by terminating one of the transactions

Monitoring and analyzing locks
Monitoring and Analyzing Locks

  • Can be difficult to identify causes for locking

  • Lock Wait Statistics notes time spent waiting for locks

    • Lock waits recorded in V$SYSTEM_EVENT

    • Lock codes defined in V$LOCK_TYPE

    • Lock waits also known as “enqueue waits”

      • Prefaced with “enq:” followed by two character lock code

    • Enterprise manager is a tool to diagnose locking

Finding sql responsible for locks
Finding SQL Responsible for Locks

  • Active Session History (ASH)

  • Active Workload Repository (AWR)

  • Can view ASH or AWR:

    • Within Oracle Enterprise Manager (OEM)

    • By querying the ASH and AWR tables directly



    • Will also show specific transaction locks

  • Need license for diagnostic pack to see above, else:



  • Use SQL Trace facility

Blockers and waiters
Blockers and Waiters

  • Often difficult to find because waits are often short in duration

    • V$ tables can change while being read

  • However, V$ tables still offer detail


    • V$LOCK


    • Use MATERIALIZE hint to take snapshot

  • For long-held locks (long-running transactions)

    • Use “Blocking Sessions” page in OEM

    • Can use third-party products

  • Use optimistic locking strategy whenever possible

Row level locking pitfalls
Row Level Locking Pitfalls

  • Unindexed Foreign Keys

  • ITL (Interested Transaction List) Waits

    • Used to store requests for row-level locks

    • Lock waits on “allocate ITL entry” is a sign of lack of free space in the ITL

    • Affected by

      • INITRANS

      • MAXTRANS

      • PCTFREE

Bitmap indexes
Bitmap Indexes

  • Can cause row-level locking to break down

  • When indexed columns being updated

    • Oracle locks all other rows in same index fragment

    • Many rows locked because bitmap indexes are small

    • Appears as row-level lock contention

Direct path inserts
Direct Path Inserts

  • Require full table or partition lock

  • Concurrent updates

    • Show heavy lock contention

    • Only one session will be working, others wait

System locks
System Locks

  • High water mark (HW) enqueue

    • Represents highest block number in table

    • Only one session can hold this lock at a time

    • Locks more frequent with LOBs or long rows

  • To avoid

    • Increase extent size

    • Increase tablespace

    • Manually allocate extents

    • Use shrink space option

Other system locks
Other System Locks

  • Space Transaction (ST) Enqueue

    • Only used for dictionary-managed tablespaces

  • Sequence Cache (SQ) Enqueue

    • Prevents multiple sessions from updating sequence cache

    • Seen when CACHE clause has small value

  • User Lock (UL) Enqueue

    • For locks created by use of DBMS_LOCK

  • See table 15-2 on p. 488