|
|
|
|
|
|
After completing this lesson, you should be able
to |
|
do the following: |
|
Define types and modes of locking |
|
List possible causes of contention |
|
Use Oracle utilities to detect lock contention |
|
Resolve contention in an emergency |
|
Prevent locking problems |
|
Recognize Oracle errors arising from deadlocks |
|
|
|
|
|
|
Automatic management |
|
High level of data concurrency |
|
Row-level locks for DML transactions |
|
No locks required for queries |
|
Varying levels of data consistency |
|
Exclusive and share lock modes |
|
Locks held until commit or rollback occurs |
|
|
|
|
|
|
DML or data locks: |
|
Table-level locks |
|
Row-level locks |
|
|
|
|
|
DDL or dictionary locks |
|
|
|
|
|
|
A DML transaction acquires at least two locks: |
|
A shared table lock |
|
An exclusive row lock |
|
The enqueue mechanism keeps track of: |
|
Users waiting for locks |
|
The requested lock mode |
|
The order in which users requested the lock |
|
|
|
|
|
Automatically acquired: |
|
Row Exclusive (RX): INSERT, UPDATE, DELETE |
|
Row Share (RS): SELECT... FOR UPDATE |
|
|
|
|
Manually acquired in LOCK statement: |
|
|
|
|
|
|
Maunally acquired in LOCK statement: |
|
Share Row Exclusive (SRX) |
|
No DML or Share mode allowed |
|
Implicitly used for referential integrity |
|
Exclusive (X) |
|
|
|
|
|
|
|
|
Exclusive DDL locks: |
|
DROP TABLE statement |
|
ALTER TABLE statement |
|
Shared DDL locks: |
|
CREATE PROCEDURE statement |
|
AUDIT statement |
|
Breakable parse locks: Invalidating shared SQL
area |
|
|
|
|
|
Unnecessarily high locking levels |
|
Uncommitted changes |
|
Other products imposing higher-level locks |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
In this lesson, you should have learned that: |
|
Queries do not lock data, unless specified in
the query |
|
DML statements use row-level and table-level
locks on tables |
|
Exclusive locks are rarely used |
|
You can monitor locks using: |
|
V$LOCK, V$LOCKED_OBJECT |
|
Oracle Enterprise Manager TopSessions |
|
Notes