1. Introduction
In this tutorial, we’ll discuss locks and latches in databases and the major differences between them.
We’ll provide helpful tips and share best practices.
2. Concurrency Control in Database Systems
Concurrency control is a technique used to maintain the integrity and performance of database systems when multiple transactions are accessing and modifying data. Even when multiple transactions execute concurrently, it ensures that the database remains in a consistent state.
When multiple transactions execute in parallel, database systems can utilize available resources more effectively and process a higher volume of requests concurrently.
3. What Are Locks?
Locks serve as synchronization mechanisms for concurrency control. They regulate access to shared data items and ensure data consistency during concurrent transactions:
A lock enables transactions to acquire read or write access to data, ensuring conflict prevention and isolation enforcement.
3.1. Lock Types
Lock types and granularity depend on concurrency control requirements, data access patterns, and isolation levels in database systems. Commonly used locks in databases and shared, exclusive, and updated locks.
A shared lock allows multiple transactions to concurrently read a data item. In the context of databases, people commonly refer to shared locks as “read locks”.
An exclusive lock grants exclusive write access to a data item. When a transaction acquires an exclusive lock on a data item, it prohibits other transactions from reading or writing that item. Exclusive locks can’t coexist with other shared or exclusive locks. That way, we ensure there will be no conflicts and guarantee exclusive access to the data item in question.
An update lock serves as an intermediate lock positioned between a shared lock and an exclusive lock. Its purpose is to enable concurrent readers while preventing conflicting updates. When a transaction possesses an update lock on a data item, it permits other transactions to acquire shared locks for reading, but it restricts them from obtaining exclusive locks.
We use update locks to safeguard against the occurrence of the “write skew” anomaly, which arises when multiple transactions concurrently update the same data and can result in incorrect results.
4. What Are Latches?
A latch is a synchronization mechanism used for concurrent access control and to ensure data consistency. Database management systems (DBMS) commonly utilize latches to actively safeguard shared resources, such as data structures or buffers, from concurrent access by multiple transactions or threads.
The design of a latch enables rapid acquisition and release, typically occurring within a single CPU cycle, which makes it highly suitable for protecting frequently accessed data structures within a DBMS:
Latches can operate in shared and exclusive modes. Shared latches permit concurrent read access by multiple transactions or threads. Exclusive latches offer exclusive write access, preventing simultaneous modifications.
4.1. Latch Acquisition
When a thread or process needs to access a shared resource protected by a latch, it requests the latch by invoking an appropriate function or system call provided by the DBMS.
When a latch is unavailable because another transaction or thread holds it, the requester can enter a spin-waiting state and repeatedly poll the latch until it becomes available. Spin-waiting helps minimize the overhead of context switching, but as the waiting time increases, it consumes CPU cycles.
4.2. Latch Release
Once a thread completes its operation on the shared resource, it releases the latch, enabling other threads to acquire it. Releasing a latch typically involves invoking a corresponding function or system call provided by the DBMS.
The DBMS informs waiting threads about the availability of a latch when it releases the latch. The waiting threads can then compete with each other to acquire the latch and proceed with their operations. Since latches are regularly acquired and released, designers typically design latch acquisition and release mechanisms to be quick and light.
5. Differences Between Locks and Latches
Locks and latches are synchronization mechanisms in database systems but serve distinct purposes and operate at different levels within the system:
Locks
Latches
Operate at a higher level of granularity; acquired on shared resources such as tables, rows, or pages.
Acquisition occurs in lower-level data structures such as buffers and data pages.
Used for concurrency control in a multi-user database system.
Used for synchronization within a single-threaded environment.
Support various modes such as shared (read) locks and exclusive (write) locks.
Typically binary in nature and only allow exclusive (write) access.
Typically held for a longer duration.
For shorter durations.
More complex and involve additional bookkeeping.
Latch acquisition and release operations are generally lightweight and efficient.
6. Best Practices and Considerations
The architecture and design of a DBMS, along with the specific requirements of the system and the resources in need of protection, determine the choice between locks and latches.
We frequently employ locks to ensure correct and atomic changes when updating client account balances or inventory levels.
When operations involve foreign key constraints or cascading updates or deletes, we use locks to maintain referential integrity. These locks prevent inconsistencies and ensure that related data remain synchronized during concurrent operations.
Latches actively participate in memory management scenarios, facilitating the allocation or deallocation of memory blocks.
During query optimization and execution phases, latches serve as active safeguards for access plans, execution context, and other internal structures that are relevant to query processing.
7. Optimizing Lock and Latch Usage for Performance
Using locks and latches requires careful consideration of the application’s requirements, the characteristics of shared resources, and the specific behavior of the DBMS.
Contentions arise when multiple threads or processes try to access the same lock or latch simultaneously, leading to delays and reduced concurrency. To minimize contention, we should avoid using a single lock or latch for an entire data structure.
Dividing the data structure into smaller units enables concurrent access, reducing contention among multiple threads. Holding locks or latches for an extended period can negatively impact concurrency and performance. Acquiring and releasing shared resources promptly improves concurrency, reduces contention, and enhances overall performance.
Lock escalation is a technique used by a DBMS to automatically convert fine-grained into coarser-grained locks to reduce overhead. For instance, a DBMS can decrease the number of locks by escalating multiple row-level locks to a table-level lock.
8. Conclusion
In this article, we talked about locks and latches. They are synchronization mechanisms that operate at different levels. Locks support various modes, such as shared (read) and exclusive (write), whereas latches only allow or prohibit exclusive access.