Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jagan.

Asked: July 07, 2003 - 7:38 am UTC

Answered by: Tom Kyte - Last updated: March 03, 2020 - 6:31 am UTC

Category: Database - Version: 8.0.6

Viewed 10K+ times! This question is

You Asked



Hi Tom


Can u explain me what is the difference between Locks,Latches,Enquees and Semaphores with an example?


Thanks
Jagan

and we said...

latches and enqueues are both types of locks.

latches are lightweight serialization devices. we try to get a latch, spin for a bit and try again. so when getting a latch, we try and try and try -- we are not told that the latch is available, we keep trying to get it (eg: not necessary a first come, first serve lock). we use latches to serialize access to in memory data structures typically (like SGA data structures)

enqueues are heavyweight serialization devices. if we cannot get an enqueue, we "go to sleep" and when the enqueue is available -- we are told about it in a first come, first serve manner. We use enqueues to perform row level locking for example.


A semaphore is an operating system supplied serialization device that one might use to implemented latching or enqueues.


So, latches and enqueues are types of locks, semaphores a programming device one might use to implement latching and enqueuing.

and you rated our response

  (17 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

2 processes spinning at the same time

July 07, 2003 - 5:10 pm UTC

Reviewer: Wim Jans from Antwerp, Belgium

Hi Tom,

Thanks for the great and useful information you provide to the Oracle community.

I'm wondering what happens when 2 processes/threads are spinning for the same latch and the latch is freed.
How does Oracle guarantuee that the 2 spinning processes don't reserve the freed latch at the same time.



Tom Kyte

Followup  

July 07, 2003 - 5:16 pm UTC

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.

locks & latches

April 29, 2008 - 10:13 am UTC

Reviewer: A reader

Hi Tom,

a) So latches are similar to Spinlocks that spins inside a tight loop for the bit to be cleared by another process?

b) Locks are similar to mutex's that gain mutual exclusion to the resource?

Are the above right?

Many thanks
- RKK
Tom Kyte

Followup  

April 29, 2008 - 11:02 am UTC

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.

distributed_lock_timeout concerns

April 29, 2008 - 7:25 pm UTC

Reviewer: Ravi Tej from Singapore

Hi Tom ,

I have few doubts on the paramater distributed_lock_timeout .
Definition :(DISTRIBUTED_LOCK_TIMEOUT specifies the amount of time (in seconds) for distributed transactions to wait for locked resources.)
My concerns are

1.Lets say SID:100 and SID:110 are there and SID 100 is holding the lock in Exclusive mode .Now would SID :110 continue to wait for ever for the Lock to be released from SID:100 or wait for definite amount of time based on the Time Specified in the
parameter distributed_lock_timeout default is 60 sec .

2.Lets say SID:100 releases the lock and it crosses 60 secs(based on the distributed_lock_timeout) and now would SID:110 still hold the lock or the process would die ?

3. Would this parameter be of some relevance if the DB server is in dedicated Mode.

4. Is there any harm in setting the value distributed_lock_timeout to a higher value 600 secs or is there any way we can check the optimal time of this parameter.

Thanks for your help

Regards
Ravi Tej

Tom Kyte

Followup  

April 30, 2008 - 9:38 am UTC

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.

ITL & latches

May 19, 2008 - 4:45 am UTC

Reviewer: RKK from INDIA

Hi Tom,

Does ITLs & latches depend on each other? How are latches associated with max(free)trans on the storage block?

Many thanks

Tom Kyte

Followup  

May 19, 2008 - 6:04 pm UTC

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.

Shared locks, Exclusive locks & Latches

June 02, 2008 - 6:05 am UTC

Reviewer: RKK from INDIA

Hi Tom,
Need more clarity on each of the following:

Shared Lock
Exclusive Lock
Latches

Is an Exclusive lock same as a latch?

Shared locks are mere Operating system defined semaphores for e.g. Sys V semaphores on linux/unix machines? - since Shared locks/semaphores allows concurrent operations on a single resource & that they are mere atomic counters - decremented/incremented atomically.

Where are latches/exclusive lock is an mutual exclusion and causes the process to sleep isn't?

So to relate these with Oracle architecture:

a) Latches/Exclusive locks are used when modifying a shared structure in SGA, is this correct?
b)Shared locks are used when a process requests a buffer to be fixed (aka pinned) to the buffer memory area in SGA for clean read only purposes, is this correct?

Regards
RKK
Tom Kyte

Followup  

June 02, 2008 - 11:28 am UTC

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>


latches and enqueues

July 09, 2009 - 4:12 am UTC

Reviewer: A reader

hi tom,

been scrolling through this page on latches and enqueues and come up with abit of own understanding on latches.

as i understand - latches are low level serialization locks that protect data in memory structures from getting corrupted

can i simply illustrate this as an example?

UPDATE emp set sal = 50 where empid = 1234;

Upon this

1) Since i need to update a block in the db buffer (which is a memory structure)
can i said that that particular buffer is latched ?

2) once the latched for that buffer is obtained, an update to the row is done (with a lock status indicated in the row piece itself)

this is what we call enqueue (higher level lock for transaction serialization purposes)

3) once the update is done, the latch is release

4) once the update is commmited the lock is release

am i right ?


Tom Kyte

Followup  

July 14, 2009 - 2:16 pm UTC

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

latches and enqueues

July 15, 2009 - 3:40 am UTC

Reviewer: A reader

hi tom,

thanks for the wonderful explanation.

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.

q1) with that ITL list in hand, why would we still need to lock the row in the row piece itself ?

since any other transactions will scan through the ITL list to see if any rows they want to modify are currently under modification or not,

if found = wait
if not found = proceed.

why is there a need to still lock the row itself then?

please advise
Regards
Noob

Tom Kyte

Followup  

July 15, 2009 - 12:17 pm UTC

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.

distributed_lock_timeout

April 28, 2010 - 3:17 am UTC

Reviewer: Deepak Sholapurkar from India

Hi Tom,
You said that database link's and two databases,are required to simulate the distributed lock timeout. But I believe distributed lock timeout can occur in Single Database RAC environment. Where multiple instances can be waiting for a row.

What is your opnion on this

Thanks & Regards
Tom Kyte

Followup  

April 28, 2010 - 8:11 am UTC

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

distributed_lock_timeout

April 29, 2010 - 2:55 am UTC

Reviewer: Deepak Sholapurkar from India

Thanks Tom,
I am having some supplimentary question on this, I have posted that in other section

In-doubt transactions

May 07, 2010 - 12:52 pm UTC

Reviewer: Badri from India

Hi Tom,

Can you please tell

1.if distributed_lock_timeout parameter has anything to do for timing out the in-doubt transactions?
2. How exactly an in-doubt transaction lock status affects readers of the affected data? (Appreciate an example for this point if possible)

Thanks in advance
Badri
Tom Kyte

Followup  

May 07, 2010 - 1:17 pm UTC

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"

Locks or latches or Enqueues?

September 25, 2010 - 2:02 am UTC

Reviewer: Deblina from India

Hi Tom,

Whenever I am in a technical fix, thou art my solution!Thank you so much for this obligation.

I am facing this problem while deleting 2 rows simultaneously from the same table, but different rows and from two different sessions.When i checked their block addresses, the two rows were from different blocks.But still there is a lock (or enqueue or latch, i don't know that so well!).Until i commit the deletion from one session, the other delete statement remains in a waiting state, even though both statements are deleting different rows of the same table.Could you please explain me what is happening here?Is there any way i could solve it, because my script requires to delete multiple rows from different sessions at the same time?May be there is some parameter at the database level which could be used(turn on, turn off, increase size, or anything else!) to solve it? Please help me.

Thanks and regards to you,
Angel
Tom Kyte

Followup  

September 27, 2010 - 11:25 am UTC

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.

A query on latches

March 28, 2013 - 12:22 pm UTC

Reviewer: Karan Chadha from Bangalore, India

Hi Tom,

Thank You for your efforts.

I have a question here related to latches -

1. As an Oracle PL/SQL developer, how can I write my code to use less number of latches? What I mean to ask here is - What operation in SQL statements and PL/SQL blocks cause the more latches to come into picture. Is it Joins, function calls, Updates etc that causes the latches. Or is it like - Developers do not need to worry about latches at all? What is the impact of Indexes on Number of latches required by an operation?


Like we know very clearly the reasons for Exclusive Locks and Deadlocks, can we know the operations responsible for causing latches ..

Thank You.

Yours,
Karan.
Tom Kyte

Followup  

March 29, 2013 - 4:00 pm UTC

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.


October 23, 2015 - 5:08 am UTC

Reviewer: Milind from India

"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. " -- From Expert Oracle Database Architecture.

Are latches also used during read operation on shared resources like library cache and database buffer cache? Or only during modifications?

Thanks in advance
Connor McDonald

Followup  

October 24, 2015 - 8:25 pm UTC

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

October 25, 2015 - 4:15 am UTC

Reviewer: Milind

Thanks a lot for the answer. The way it is explained is very nice and will have long lasting memory. Thanks!

October 26, 2015 - 6:23 pm UTC

Reviewer: Alexander

I'm not trying to be pedantic, but I'm just curious why the two terms (latch, lock). Since a latch is just a fast lock, I wonder why someone ever felt the need to differentiate between the two. Ie is the speed in which a lock is applied and removed of any concern to us? Just a thought.
Chris Saxon

Followup  

October 27, 2015 - 9:48 pm UTC

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.


August 31, 2016 - 9:05 am UTC

Reviewer: A reader

I fire below three queries .


select c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.USERNAME,
b.TERMINAL,
b.PROGRAM,
b.osuser,
b.machine
from v$locked_object a, v$session b, dba_objects c
where b.sid = a.session_id
and a.object_id = c.object_id;
output some time display inactive session and
some time display active session.

2) select * from dba_blockers;
output :- null

3) select * from dba_waiters;
output :- null

Now My question is if lock exits then why not catch up in dba_blockers and waiters table or
if v$locked_object shows locks on objects so how to determine what types of lock occurs?

Connor McDonald

Followup  

September 01, 2016 - 2:39 am UTC

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



Causes Of latches and mutex contention

February 26, 2020 - 7:47 am UTC

Reviewer: Pradeep Prajapati from India

Dear Team,

Please help me with some of scenario on which latch and mutex wait events are occurred apart from hot block in db buffer.

Thanks
Pradeep
Connor McDonald

Followup  

March 03, 2020 - 6:31 am UTC

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.