This article is part of Oracle Performance Tuning Series and is created to describe Oracle’s locking mechanism.

A lock is a resource that you need to hold to get access to the resource. Oracle has two kind of locks: enqueues and latches, we would now focus on these locking mechanism individually.

Enqueues

Enqueues are sophisticated locks for managing access to shared resources (like tables, rows, jobs, redo threads). An enqueue can be requested in different levels/mode:

• Null
• Row share
• Row exclusive
• Share
• Share row exclusive
• Exclusive

If a session holds an enqueue in share mode, other sessions can then also take the enqueue in share mode (for the same resource). If a session holds an enqueue in exclusive mode, other sessions that wants to get it – independently in which level – they have to wait.

When access is required by a session, a lock structure is obtained and a request is made to acquire access to the resource at a specific level (mode) is made. The lock structure is placed on one of three linked lists which hang off of the resource, called the OWNER (if the enqueue could be acquired), WAITER (if the session is waiting to acquiring the enqueue) and CONVERTER (the session is holding the enqueue in one level but wants to convert it to another) lists.

Following are some common type of enqueues:-

• TM – Every time a session wants to lock a table, a TM enqueue is requested. If a session deletes a row in the parent-table and a referential constraint (foreign key) is created without an index on the child-table, or if the session is updating the column(s) that the foreign key references to, then a share lock is taken on the child table. If another session tries to do changes to the child-table they have to wait (because they want the enqueue in row exclusive mode, and that is not compatible with the share mode).
• TX – As soon as a transaction is started a TX enqueue is needed. A transaction is uniquely defined by the rollback segment number, the slot number in the rollback segment’s transaction table and the slot number’s sequence number. A session can be waiting on a TX enqueue for several reasons:
  1. Another session is locking the requested row
  2. When two sessions tries to insert the same unique key into a table (none of them has done a COMMIT), then the last session is waiting for the first one to COMMIT or ROLLBACK.
  3. There are no free ITL (Interested Transaction List) in the block header.
• UL – A session has taken a lock with the DBMS_LOCK.REQUEST function.

Latches

Latches provide a low level serialization mechanism protecting shared data structures in the SGA. A latch is a type of a lock that can be very quickly acquired and freed. Latches are typically used to prevent more than one process from executing the same piece of code at a given time. When a latch is acquired, it is acquired at a particular level in order to prevent deadlocks. Once a process acquires a latch at a certain level, it cannot subsequently acquire a latch at a level that is equal to or less than that level (unless it acquires it nowait). Associated with each latch is a cleanup procedure that will be called if a process dies while holding the latch. This cleaning is done using the services of PMON. The underlying implementation of latches is operating system dependent, particularly in regard to whether a process will wait for a latch and for how long.

The goal of latches is to manage concurrent access to shared data structures such that only one process can access the structure at a time. Blocked processes (processes waiting to execute a part of the code for which a latch has already been obtained by some other process) will wait until the latch is released. Oracle uses atomic instructions like “test and set” for operating on latches. Since the instructions to set and free latches are atomic, the OS guarantees that only one process gets it and because it is only a single instruction, it is quite fast. 

Latch requests can be made in two modes:

• willing-to-wait : A “willing-to-wait” mode request will loop, wait, and request again until the latch is obtained.
• no wait : In “no wait” mode, the process will request the latch and if it is not available, instead of waiting, another one is requested. Only when all fail does the server process have to wait.

Difference between Latches & Enqueues

One difference between a latch and an enqueue is that the enqueue is obtained using an OS specific locking mechanism whereas a latch is obtained independent of the OS. An enqueue allows the user to store a value in the lock (i.e the mode in which we are requesting it). The OS lock manager keeps track of the resources locked. If a process cannot be granted to the lock because it is incompatible with the mode requested and the lock is requested with wait, the OS puts the requesting process on a wait queue which is serviced in FIFO.

Another difference between latches and enqueues is that enqueues maintain and ordered queue of waiters while latches do not. All processes waiting for a latch concurrently retry (depending on the scheduler). This means that any of the waiters might get the latch and conceivably the first one attempting to obtain the latch might be the last one to actually get it. Latch waiters may either use timers to wakeup and retry or they may spin (only in multiprocessors) spinning on a latch means holding the CPU and waiting in anticipation of that latch being freed in a short time).