1 / 12

Chapter 15 Lock Contention

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

percy
Download Presentation

Chapter 15 Lock Contention

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 15Lock Contention

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

  3. Lock Types and Modes (cont.) • Lock modes • Shared row • Exclusive row • Shared table • Shared row exclusive • Table • See table 15-1 on p. 461

  4. Waiting for Locks • By default, locks are released by: • COMMIT • ROLLBACK • Waiting for locks degrade performance • Sessions can choose whether to wait for lock • NOWAIT clause • WAIT n clause (where n = number of seconds) • SKIPPED LOCK • 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

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

  6. 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 • V$ACTIVE_SESSION_HISTORY • DBA_HIST_ACTIVE_SESS_HISTORY • Will also show specific transaction locks • Need license for diagnostic pack to see above, else: • View APPLICATION_WAIT_TIME of V$SQL • View V$SEGMENT_STATISTICS • Use SQL Trace facility

  7. 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$SESSION • V$LOCK • V$WAIT_CHAINS • 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

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

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

  10. Direct Path Inserts • Require full table or partition lock • Concurrent updates • Show heavy lock contention • Only one session will be working, others wait

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

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

More Related