What is the difference between a lock and a latch in the context of concurrent access to a database?
From CMU 15-721 (Spring 2016), lecture 6 presentation, slides 25 and 26, which cites A Survey of B-Tree Locking Techniques by Goetz Graefe:
Locks
→ Protects the index’s logical contents from other txns.
→ Held for txn duration.
→ Need to be able to rollback changes.
Latches
→ Protects the critical sections of the index’s internal data structure from other threads.
→ Held for operation duration.
→ Do not need to be able to rollback changes.
It really depends on your DBMS, but here's a good explanation for Oracle.
http://www.dba-oracle.com/t_lru_latches.htm
Latches are like locks for RAM memory structures to prevent concurrent access and ensure serial execution of kernel code. The LRU (least recently used) latches are used when seeking, adding, or removing a buffer from the buffer cache, an action that can only be done by one process at a time.
Following is from SQL Server stand point.
Latches are short-term light weight synchronization objects. Unlike locks, latches do not hold till the entire logical transaction. They hold only on the operation on the page
.
Latches are used by the engine for synchronization of multiple threads (for example trying to insert on a table). Latches are not for developer or application - it is for the engine to do it's task. Latches are internal control mechanism. Whereas locks are for the developer and application to control. Latches are for internal memory consistency. Locks are for logical transactional consistency.
Waits caused by latches are very important for diagnosing performance issues. Take a look at Diagnosing and Resolving Latch Contention on SQL Server - Whitepaper. The PAGEIOLATCH_EX
is an important wait type.
References