Overview

We have all heard that Oracle has a great Locking mechanism, way superior than other databases. While in different databases, readers can block writers and writers can block readers, Oracle doesn't allow that by default. In oracle you can select without a problem (although there is an exception to that rule) and you can write without being afraid that someone is selecting your data.

The hearth of this mechanism is the Multi Versioning. Meaning Oracle keeps several copies of the same data which is consistent with different points of time. For example, one row can be consistent with 12:00 and other row can be consistent with 11:50. Both of these rows are in the buffer cache.

That is why when you start a select you are having a CONSISTENT (with the point of time your query started) GET and when you do DML you are having a CURRENT (the latest version of the data) GET.

In order to protect and achieve that mechanism oracle is using several structures:

• Latches
• Mutexes
• Enqueues (Generally known as Locks)

Let's examine each of the structures now:

Latches are lightweight serialization control structure devices. WoW that was a lot of terms used here :) So let's explain it a little bit:

• lightweight - Meaning they don't cause a lot of overhead to support. They are generally around 100-200 bytes of data in the SGA and they hold between 150-200 instructions)
• Serialization - Meaning they can process only one request at a time
• Control - They control access the shared resources (Library caches objects mostly: cursors and etc.)

Latches also come in 2 flavors:

• Shared read (Common in 9i, although you can also get exclusive shared read….don't ask :D )
• Exclusive

Latches also can be categorized in two ways regarding their activity:

• Willing to wait (Most of them)
• Immediate gets

Latch usually start as immediate and then transform into willing to wait, but that is a story for another time :)

In a nutshell, Latch is a combination of memory location in the SGA (around 100-200 bytes big) and CPU atomic capability of checking and changing the value in that location. They can be held only by one process and all else has to wait. It is also import to notice that the latch structure doesn't allow queuing. Meaning, whoever gets it first, its his :)

The logic of the latch is:

“If I can set the latch memory location to some value N, then I can do something with the structure that the latch is protecting.”

To obtain a latch a session is usually doing the following (depends on the request: Willing to wait or exclusive), but the logic is pretty similar:

1. Set register X to point at latch address A
2. If value at address A is zero set it to 0xff ***
3. If the value at address A is set to 0xff then you “own” the latch
4. If not then go back to the top and try again—for a couple of thousand attempts

We were speaking of what latches are and how to obtain then. But what really they protect. Well Latches protect shared resources, mostly library caches objects, such as cursors. Library objects and objects in general, in Oracle, are chained into linked lists, that is a structure which keeps the location of all members.

Meaning that each member knows the location of the next member. Each linked list has a hash bucket (function) which acts as a quick search option. So if we want to know if a certain cursor is in a certain linked list we check the hash value of our select through the “hash bucket”. If the hash value match, then the cursor has to be in that linked list.

In Oracle ⇐10g and even in early 11g. A lot of hash buckets and their linked lists were protected by a single latch. Of course that create a lot of contention even for a simple selects.

You can observer graphical representation of that structure:

Graphical representation of how latch is working can be seen below:

Latches are good control structures but they have their flows. For example if the session fails to get the latch it will spin couple thousand times before going to sleep, That spinning is causing CPU time which can be spend for other more “productive job”.

It is worth mentioning that latches aren't used so much in newer Oracle version: >=11g. They were replaced by other structure called MUTEX, which is way more optimal and efficient :)