chapter 15 lock contention
Skip this Video
Download Presentation
Chapter 15 Lock Contention

Loading in 2 Seconds...

play fullscreen
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
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