DBA Hub

📋Steps in this guide1/18

Understanding Latches, Locks & Enqueues in Oracle

Learn the differences between Oracle latches, locks, and enqueues with simple explanations and practical examples. A must-read guide for Oracle DBAs to understand concurrency, performance, and database contention.

oracle configurationintermediate
by OracleDba
17 views
1

1. What Are Latches in Oracle?

Latches are low-level serialization mechanisms used to protect in-memory shared data structures in the SGA. Think of a latch as a very fast, short-duration lock used internally by Oracle.
2

Key Characteristics

- Lightweight and in-memory Lightweight and in-memory - Very fast (microseconds) Very fast (microseconds) - Not transactional Not transactional - Held for a very short time Held for a very short time - Protect shared memory structures (e.g., library cache, buffer cache) Protect shared memory structures (e.g., library cache, buffer cache)
3

Real-World Example

When multiple sessions try to read or write from the buffer cache , Oracle uses buffer cache latches to avoid corruption.
4

Common Latch Contention Symptoms

- High CPU usage High CPU usage - Library Cache: Pin or Lock latch waits Library Cache: Pin or Lock latch waits - Query execution delays Query execution delays
5

Useful Views

Latches = Speed + Protection (not related to user transactions)
6

2 . What Are Locks in Oracle?

Locks are used to control access to data in the database and maintain transaction isolation . These are DML locks that ensure two users don’t change the same data at the same time.
7

Key Characteristics

- Protect rows, tables, and objects Protect rows, tables, and objects - Held for the duration of a transaction Held for the duration of a transaction - Enforce data consistency Enforce data consistency - Acquired automatically by Oracle Acquired automatically by Oracle
8

Example

Session A updates a row: Session B tries to update the same row → row lock wait .
9

Types of Locks

- Row-level locks (TX) Row-level locks (TX) - Table locks (TM) Table locks (TM) - DDL locks DDL locks - Internal locks Internal locks
10

Useful Views

Locks = Protecting data during transactions
11

3. What Are Enqueues in Oracle?

Enqueues are more advanced, flexible locking mechanisms that follow a queue structure — hence the name “enqueue”. They are used for transactional locks, schema locks, and inter-instance coordination (RAC).
12

Key Characteristics

- Follow a queue mechanism Follow a queue mechanism - Can be held in different modes (NULL, Share, Exclusive, etc.) Can be held in different modes (NULL, Share, Exclusive, etc.) - Can persist for longer periods Can persist for longer periods - Protects multiple database resources Protects multiple database resources
13

Example

- When a session updates a row → TX enqueue When a session updates a row → TX enqueue - When a session executes DDL → TM enqueue When a session executes DDL → TM enqueue
14

Useful View

Enqueues = Structured, queued locks for multiple resource types Simple Explanation: - Latches → Protect RAM Latches → Protect RAM - Locks → Protect Data Locks → Protect Data - Enqueues → Protect Resources Enqueues → Protect Resources
15

Latch Waits

- -
16

Lock Waits

- -
17

Enqueue Waits

- - -
18

Kill a Blocking Session

Understanding the difference between latches, locks, and enqueues is essential for any Oracle DBA. They help keep the database consistent , stable , and performant , even when thousands of sessions are running concurrently. Explore more with Learnomate Technologies! Want to see how we teach? Head over to our YouTube channel for insights, tutorials, and tech breakdowns: www.youtube.com/@learnomate To know more about our courses, offerings, and team: Visit our official website: www.learnomate.org Interested in mastering Oracle Database Administration? Check out our comprehensive Oracle DBA Training program here: https://learnomate.org/oracle-dba-training/ Want to explore more tech topics? Check out our detailed blog posts here: https://learnomate.org/blogs/ And hey, I’d love to stay connected with you personally! Let’s connect on LinkedIn: Ankush Thavali 😎

Comments (0)

Please to add comments

No comments yet. Be the first to comment!