SQL Server – Differences Between Latch and Lock

latchlockingsql server

Recently when i was trying to find information about latch_ex wait type, i come across one blog as below which stated about latch and lock.

enter image description here

After reading this blog , i m just curious in one thing. When app submit a request, SQL server will look for the information in buffer cache first and if the page not in buffer cache ,then only it will read from disk and put it in buffer cache before send info to app. My question based on the screenshot as above where it stated latch and lock need to avoid two thread updating same page. Basically all request come to SQL server will go to buffer cache first, if the page in buffer cache is busy updating, another thread will have to wait. It wont go back to disk because the page already in memory. Then what is the purpose of lock since every request will be done through memory and there is latch to protect the page

Best Answer

I don't really understand what you don't understand in the quote you included (strangely, as a screenshot), as it explains the difference quite clearly, in my opinion.

Locks and latches have different scopes and lifecycles. Locks apply to what you might call database physical model elements -- tables, rows, index entries. Latches protect various memory structures the database server uses when executing SQL statements or performing its housekeeping tasks.

A transaction might hold one, multiple, or no locks at all on the objects it is processing, which signals to other transactions what access they can have to those objects. An object protected by a lock doesn't have to be "in memory"; for example, a table protected by a table-level lock may not even have any of its pages present in the bufferpool.

Worker threads acquire and release latches to strictly prevent other concurrently running threads (that may be executing tasks within the same transaction, other transactions, or on behalf of some server background process) from simultaneously accessing certain memory areas. For example, two transactions may hold locks for different rows on the same bufferpool page, which would not prevent them from concurrently accessing their respective rows, if it weren't for the page latch that ensures the entire page remains consistent for all readers and writers. And then there is a lazy writer process, which couldn't care less about any of those locks but still must acquire a latch before it can write a consistent page out to disk.

In other words, locks are a transaction synchronisation mechanism while latches help synchronise processes or threads.

Related Question