• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2024
  • Last Modified:

Latches and locks

What is  the difference between latches,locks and semaphore in oracle ?
0
amolghadge
Asked:
amolghadge
1 Solution
 
JacekMychaCommented:
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
0
 
Sowmya_KCommented:
Latch:
====
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.

Locks/Enqueues:
===========
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.

Semaphore:
========
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
0
 
KrishyogiCommented:
Latching
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).
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now