Oracle Core # Locks and Latches

I am reading Jonathan Lewis’s latest book Oracle Core recently.

Something from chapter Locks and Latches:

0. First things, first…a few graphs

1. Lock and latch usage cases: lock -> objects; latche -> shared memory

locks tend to be used to protect objects, while latches tend to be used to protect shared memory, so the latch activity tends to be highly concurrent and very quick.

2. Lock and Pin usage cases: lock -> hold an object; pin -> use an object

Locks and Pins

The KGL pin comes into play when you actually use an object. Although a KGL lock will hold an
object in memory, there are parts of the object that are dynamically re-creatable (the execution plan for
an SQL statement, for example), and these can still be discarded if there is a heavy demand for memory
even if you have a KGL lock in place.

However, when you are actually using an object (running an SQL statement, say), you need to
ensure that the re-creatable bits can’t be pushed out of memory, so you pin the object to protect them.

3. What’s the difference between a lock and an enqueue? lock = enqueue

If you’re ever asked, “What’s the difference between a lock and an enqueue?” the answer is “nothing.”
They are two terms for the same concept, even though, historically, the documentation tended to use the term
“lock” while the internal structures were called “enqueues” and the instance activity stats referred to “enqueues”;
for example, if you query the dynamic performance view v$lock, you are querying the structure defined by the
parameter enqueues. To add to the confusion, there are structures called KGL locks, or breakable parse locks,
which are never called enqueues and which people don’t generally think of when you mention locks.

4. How expensive lock and pin?

Another issue with the KGL locks and KGL pins was that, to use them, you had to constantly
manipulate linked lists, attaching and detaching chunks of memory, and you had to do this while
holding a latch exclusively (and, as I’ve pointed out, there are surprisingly few latches for the library
cache). So for very busy systems, the whole lock/pin issue could become a significant scalability threat.
...
The mechanisms of locking and pinning in the library cache allowed two problems to creep in. First, there
aren’t many latches covering all the work that has to be done; second, it’s quite a lot of work to create a
packet of memory, label it properly, and insert it into a linked list (or, conversely, to remove a link from a
linked list and put it back in the shared pool).

5. Visible of lock

There are two main areas where this locking is made visible: in v$lock, where we see enqueues attached to enqueue resources,
and in v$open_cursor, where we see KGL locks attached to library cache objects
(we also have KGL pins attached to library cache objects, but they are not exposed in any dynamic performance views).

6. New mechanism to improve concurrency: mutex

However, as we moved through 10g to 11g, the whole KGL lock/KGL pin mechanism was gradually
replaced by the mutex mechanism.
...
As you can see from these comments, mutexes (which started to appear in Oracle Database 10g
most significantly as a replacement for pins in the library cache) fall somewhere between locks and
latches: sessions fight over mutexes the way they fight over latches, but sessions can hold mutexes for
long periods just as they can hold locks (and pins) for a long time.
...
a mutex is very similar to a latch in the way it is implemented and used.
...
Essentially a mutex is a “private mini-latch” that is part of the library cache object. This means
that instead of a small number of latches covering a large number of objects—with the associated risk of
competition for latches—we now have individual mutexes for every single library cache hash bucket, and
two mutexes (one to replace the KGL pin, the other related in some way to handling dependencies) on
every parent and child cursor, which should improve the scalability of frequently executed statements.
...
11.2 got rid of all the little packets of memory and the
linked lists—in fact, it even got rid of the latches used to protect the hash chains, and replaced them all
with mutexes.

A mutex is a “micro-latch” and its usage involves a very short code path. Like a latch, it’s just a counter
that can safely be incremented to show “one more person is interested” or can be modified with a similar
“high-bit” approach to say “I’m waiting for exclusive access” followed by a different bit saying “I’ve got
exclusive access” when everyone else has counted themselves off the mutex.

If you have mutexes on hash buckets, you have one micro-latch per bucket instead of a maximum of 67
latches covering (in my case) 131,072. If you have a mutex on each library cache object to represent the
KGL locks and another to represent the KGL pins, then you don’t have to do all that memory allocation and
deallocation and don’t have to run code to connect things to linked lists. (In fact, both locks and pins still
exist as structures in 11.2, but Oracle Corp. has rewritten the code for allocating and freeing them; it is,
however, possible that they will eventually disappear completely.)