Chapter 6: Interpreting Locks-Related Wait Events

Overview

Concurrent database access in its simplest form can be defined as multiple sessions simultaneously reading from or writing to the database. The Oracle SGA architecture can be a lot simpler if concurrent access is the only goal of the RDBMS. However, there is another goal that supersedes concurrency in terms of importance and significance: data integrity.

The integrity of any data structure in the SGA cannot be guaranteed if multiple processes are allowed to modify the same data structure at the same time. Oracle ensures data integrity by serializing access to SGA data structures and database objects with latches and locks. In the DBMS concept, this is the ‚“I ‚½ of the ACID properties (Atomicity, Consistency, Isolation, and Durability). Isolation is the safeguard that prevents conflicts between concurrent transactions. So, what happens to concurrency? In this case, concurrency must be sacrificed for the sake of integrity.

Unfortunately, most developers who attempt to improve application performance by increasing the level of concurrency are not aware of the serialization factors. This misunderstanding usually results in poorer application performance because more processes must compete for serialized resources such as latches and contented resources such as locks. The symptoms are usually latch free , buffer busy waits , and enqueue waits. This chapter tells you how to diagnose and solve problems related to locking and serialization.