[bbk3204] 第67集 -Chapter 17-Monitoring and Detecting Lock Contention(00)

Objectives

After completing this lesson,you should be able to do the following:

• Define levels of locking
• Identify causes of contention
• Prevent locking problems
• Use Oracle utilities to detect lock contention
• Resolve contention in an emergency
• Resovle deadlock conditions

Modes of Locking

Oracle Database uses two modes of locking in a multiuser database:

• Exclusive lock mode prevents the associates resource from being shared.This lock mode is obtained to modify data.The first transaction to lock a resource exclusively is the only transaction that can alter the resource until exclusive lock is release.
• Shared lock mode allows the associated resource to be shared.depending on the operations involved.Multiple users reading data can share the data,holding share locks to prevent concurrent access by a writer (who needs an exclusive lock).Several transaction can acquire share locks on the same resource.

Locking Mechanism

• Automatic management
• High level of data concurrency
  • -Row-level locks for DML transactions
  • -No locks required for queries
• Multi-version consistency
• Exclusive and Share lock modes
• Locks held unitl commit or rollback operations are performed

Data Concurrency

Three Types of Lokcs

• DML or data locks:
  • -Table-level locks(TM)
  • -Row-level locks(TX)
• DDL or dictionary locks
• Latch locks

DML Locks

A DML transaction gets at least tow locks:

• A shared table lock
• An exclusive row lock

Enqueue Mechanism

The enqueue mechanism keeps track of:

• Users waiting for locks
• The requested lock mode
• The order in which users requested the lock