Latches and locks

Posted on 2006-05-23
Last Modified: 2012-06-27
What is  the difference between latches,locks and semaphore in oracle ?
Question by:amolghadge
    LVL 3

    Expert Comment

    Read "Oracle Database Concepts" -> Chapter "Data Concurency and Consistency" -> "How Oracle Locks Data".

    Latches are simple, low-level serialization mechanisms to protect shared data structures in the system global area (SGA). For example, latches protect the list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache. A server or background process acquires a latch for a very short time while manipulating or looking at one of these structures. The implementation of latches is operating system dependent, particularly in regard to whether and how long a process will wait for a latch.
    >>Generally, we don't care about latches. They work automatically.

    Locks are mechanisms that prevent destructive interaction between transactions accessing the same resource—either user objects such as tables and rows or system objects not visible to users, such as shared data structures in memory and data dictionary rows. In all cases, Oracle automatically obtains necessary locks when executing SQL statements, so users need not be concerned with such details. Oracle automaticallyuses the lowest applicable level of restrictiveness to provide the highest degree of data concurrency yet also provide fail-safe data integrity. Oracle also allows the user to lock data manually.
    >>There are many different types of locks: DDL locks, DML locks, internal locks, user locks. They can be aquired automatically or manually (explicitly issuing a LOCK statement), they can be exclusive or shared. They last much longer than latches, until the end of transaction. Generally, we should know how they work, because they are important.

    >>There are no semaphores in Oracle. A semaphore is an operating system mechanism. Oracle uses semaphores to implement latches (on some platforms).

    Jacek Mycha
    LVL 4

    Accepted Solution

    A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET are used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until they obtain a resource, or time out.

    Enqueues and locks are different names for the same thing. Both support queuing and concurrency. They are queued and serviced in a first-in-first-out (FIFO) order.

    Semaphores are an operating system facility used to control waiting. Semaphores are controlled by the following Unix parameters: semmni, semmns and semmsl. Typical settings are:

    semmns = sum of the "processes" parameter for each instance (see init.ora for each instance)
    semmni = number of instances running simultaneously;
    semmsl = semmns

    Expert Comment

    SQL Server uses latches to provide data synchronization. A latch is a user-mode reader-writer lock implemented by SQL Server. Each data page in memory has a buffer (BUF) tracking structure. The BUF structure contains status information (Dirty, On LRU, In I/O) as well as a latch structure.
    Locking maintains the appropriate lock activity; latching controls physical access. For example, it is possible for a lock to be held on a page that is not in memory. The latch is only appropriate when the data page is in memory (associated with a BUF).

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now