using instructions like a "test and set" or some such thing -- chips and operating systems provide us the mutual exclusion devices we need to do these atomic operations, fast.

so the answer is very much OS dependent -- many chipsets provide a "test and set" or equivalent instruction.

latches are spinlocks mostly - yes.

a latch is a mutex, mutual exclusion device.


locks are a mutex as well.

both are a MUTUAL EXCLUSION device, each are implemented differently.

you need a database link for this, you have two sessions, presumably in the same database - distributed lock timeout does not come into play without a distributed environment.

a latch is a mutual exclusion device, used to serialize access to something, to make sure modifications are done safely.

an ITL is data stored on the block, we use latches to protect modifications to the ITL.


latches have nothing to do with the data on the block - other than that internally we use latches to serialize modifications to that shared bit of data that is the block itself.

latches are a type of lock, a serialization device, a mutual exclusion device.

enqueue locks (both shared and exclusive) are the same thing. A shared lock is just a lock that more than one thing can hold at the same time - many people may have a shared table lock on table T at the same time. It would prevent anyone from getting an exclusive table lock on T. If someone did get an exclusive table lock on T - no one else can get a shared or exclusive lock on it.



<quote src=Expert Oracle Database Architecture>

Latches are lightweight serialization devices used to coordinate multiuser access to shared data structures, objects, and files.

Latches are locks designed to be held for extremely short periods of time¿for example, the time it takes to modify an in-memory data structure. They are used to protect certain memory structures, such as the database block buffer cache or the library cache in the shared pool. Latches are typically requested internally in a ¿willing to wait¿ mode. This means that if the latch is not available, the requesting session will sleep for a short period of time and retry the operation later. Other latches may be requested in an ¿immediate¿ mode, which is similar in concept to a SELECT FOR UPDATE NOWAIT, meaning that the process will go do something else, such as try to grab an equivalent sibling latch that may be free, rather than sit and wait for this latch to become available. Since many requestors may be waiting for a latch at the same time, you may see some processes waiting longer than others. Latches are assigned rather randomly, based on the luck of the draw, if you will. Whichever session asks for a latch right after it was released will get it. There is no line of latch waiters¿just a mob of waiters constantly retrying.

Oracle uses atomic instructions like ¿test and set¿ and ¿compare and swap¿ for operating on latches. Since the instructions to set and free latches are atomic, the operating system itself guarantees that only one process gets to test and set the latch even though many processes may be going for it simultaneously. Since the instruction is only one instruction, it can be quite fast. Latches are held for short periods of time and provide a mechanism for cleanup in case a latch holder ¿dies¿ abnormally while holding it. This cleanup process would be performed by PMON.

Enqueues, which we discussed earlier, are another, more sophisticated serialization device used when updating rows in a database table, for example. They differ from latches in that they allow the requestor to ¿queue up¿ and wait for the resource. With a latch request, the requestor session is told right away whether or not it got the latch . With an enqueue lock, the requestor session will be blocked until it can actually attain it.

Note Using SELECT FOR UPDATE NOWAIT or WAIT [n], you can optionally decide not to wait for an enqueue lock if your session would be blocked , but if you do block and wait, you will wait in a queue.

As such, an enqueue is not as fast as a latch can be, but it does provided functionality over and above what a latch can offer. Enqueues may be obtained at various levels, so you can have many share locks and locks with various degrees of shareability.

</quote>

1) no, you would say "the data structures that are used to hold the buffers are latched". That is, in the buffer cache there is an array of pointers to lists of blocks. In order to get a block out of one of these lists, we latch the list (cache buffers chains latch).

We take the DBA (data block address) you are looking for and hash it to a number between 1 and number of arrays of lists in the cache. We then 'latch' that list so we can walk it and look for your block.

To update the block, you would get it in current mode - only one transaction can get a block in current mode (an attribute of the block) at a time.


2) when you modify the block, you fill in the ITL (interested transaction list) information in the header. When someone else wants to update that block - they get the block and look to see if any ITL entries point to a row they want to modify. If they do - they look to see if that transaction is committed or not. If the transaction is committed - they continue one. Else, if the transaction is still open, then ENQUEUE (get in line) on that transaction. When it commits, they'll be released and can retry their modification.

3) the latch was long gone - you might get and release thousands of latches during the processing of the update. An update takes way too long to hold a latch for the entire duration of the statement.

4) the waiters will be released, yes

q1) because that is exactly how you lock a row.

You make an entry in the transaction list in the block header.

If you didn't make such an entry - how would anyone KNOW that you were interested in that row and processing it???!@?!?!


That is how you lock a row - you put information in the block header about what you are doing.

well, you only need one database and a database link actually. I didn't say two databases...


LMD0 takes care of that in RAC - the cache access is coordinated a bit differently than a distributed transaction

1) in doubt transactions happen when the participants in a two phase commit have VOTED to commit - but haven't received the command to commit from the coordinator - either because they crashed themselves, the coordinator crashed, or communication was otherwise broken.

it isn't a time out thing, it is a failure thing. the lock timeout isn't involved - that init.ora parameter sets how long a client will wait to obtain a lock from a remote system.

2) they can still read it, locking it would be prevented as it will remain locked while "in doubt"

there is something else going on here - a trigger, a foreign key that is not indexed, something.

this is what you need to do.

step 1) create table, put two rows in it, prove to yourself from two sessions that you can delete both rows without blocking.

step 2) start making this tiny example look like YOUR case. If it has a child table or is one, create the other table. put sample data in there. mimic the transaction. keep adding bits till - it blocks. Now you know what the cause is.

Then we can work on this.

1) It depends, use something like runstats (on a single user test database - to avoid counting other sessions latching activity) to measure things like this. search this site for runstats for examples..

Yes, developers should worry about latches.

for example - hard parsing causes a ton of serialization (latching, mutexes). avoid it - soft parsing causes less latching, that is preferred over hard parsing - but!!!! no parsing uses the least number of latches (none) and is preferred over repeated soft parsing!


for example



to understand what *methods* - what approaches you take to the database - utilize the most latches and the types of latches, you'll have to benchmark.

Here's a terrible metaphor, but it does explain the concept :-)

Why do we have a (real physical) latch on the door of a toilet ? It's to avoid an embarrassing situation of someone walking in on someone else.

It doesnt really matter if the person who was in there was doing nothing (eg a 'read') or actually making use of the facilities (eg a 'write') - in either case, they want some assurances that no-one is going to burst in on them.

So they use a latch.

Same with Oracle - I might only be reading memory, but I need to protect what I'm reading because someone might "burst in" and want to change that memory whilst I'm reading it

A latch has a different implementation to a "lock". They are also used in different ways (for example, you can't issue a "latch table" statement, but you can "lock table").

Different names make it easier to know what you're talking about. If they were all called lock then you'd always be qualifying it.

One particular difference to the lock and latch is the queueing semantics.

When you request a lock, you enter a queue. If you are first, then you get the lock. If you are not first, you wait in line and (hopefully) you will eventually get to the front of the queue and get the lock. But it is a *queue*.

Requesting a latch is not a queue. You might try get a latch and fail...you go to sleep for a little while. Meanwhile someone else could arrive *just now* and request the latch and get it while you were sleeping. This is why its more lightweight to implement - its basically a 'free for all' for whoever is trying for the latch.

There is a difference between L-ocking and BL-ocking.

For example

SQL> delete from t where rownum = 1;

1 row deleted.

So I've delete a row, so I obviously have it locked. But is that blocking anyone ? No.

SQL> select c.owner,
  2  c.object_name,
  3  c.object_type,
  4  b.sid,
  5  b.serial#,
  6  b.status,
  7  b.USERNAME,
  8  b.TERMINAL,
  9  b.PROGRAM,
 10  b.osuser,
 11  b.machine
 12  from v$locked_object a, v$session b, dba_objects c
 13  where b.sid = a.session_id
 14  and a.object_id = c.object_id;

OWNER                          OBJECT_NAME                              OBJECT_TYPE                    SID    SERIAL# STATUS
------------------------------ ---------------------------------------- ----------------------- ---------- ---------- --------
USERNAME             TERMINAL         PROGRAM                        OSUSER
-------------------- ---------------- ------------------------------ ------------------------------
MACHINE
----------------------------------------------------------------
MCDONAC                        T                                        TABLE                        20 11757 ACTIVE
MCDONAC              COMCDONA-AU      sqlplus.exe                    COMCDONA-AU\comcdona
ORADEV\COMCDONA-AU


SQL> select * from dba_blockers;

no rows selected

We don't use mutexes for the buffer cache

SQL> select * from V$MUTEX_SLEEP;

MUTEX_TYPE                       LOCATION                                     SLEEPS  WAIT_TIME     CON_ID
-------------------------------- ---------------------------------------- ---------- ---------- ----------
Row Cache                        [19] kqrpre                                       8      10586          0
Row Cache                        [17] kqrCreateUsingSecondaryKey                   4      10246          0
Library Cache                    kglini2      157                                  4         24          0
Library Cache                    kglScanDS2   146                                  3      10794          0
Library Cache                    kglScanDS 132                                     4      21691          0
Library Cache                    kglllal3 111                                      1          2          0
Library Cache                    kglhdgn2 106                                    111     194084          0
Library Cache                    kglpnal1  90                                      4         50          0
Library Cache                    kgllkdl1  85                                     16      21254          0
Library Cache                    kglhdgh2  65                                      1         12          0
Library Cache                    kglhdgh1  64                                      5       1184          0
Library Cache                    kglhdgn1  62                                      4      10643          0
Library Cache                    kgllkc1   57                                    121     205307          0
Library Cache                    kglati1   45                                      3       9739          0
Library Cache                    kglrdtin1 44                                     11      30309          0
Library Cache                    kglpin1   4                                       8        590          0
Library Cache                    kglget2   2                                      14      21484          0
Cursor Parent                    kkscsAddChildNode  [KKSPRTLOC28]                  5         26          0
Cursor Parent                    kksLoadChild [KKSPRTLOC4]                         5       9344          0
Cursor Pin                       kkslce [KKSCHLPIN2]                             286     512359          0

A hot block might be impacted by latch content, it might not be.

If two people both want to *read* the same block, then on some platforms, we have a "sharing" mechanism..ie, both can share the latch without contention.

But if one of us want to modify a block (or the chain that links the blocks) whilst someone else is reading it, then we'll need a latch to protect against that.

The most common example is heavy insert of closely related values into an table, where "closely related" is defined by an index on on that column.