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