This is not a detailed in-depth dive into locks nor latches , but a short summary to highlight the difference and to show that they are different but do affect each other and can manifest in the same way for the end user (by causing waits). For more information I have listed some resources I have found helpful in this article to explain what locks and latches are.
Locks
Locks are held on rows, pages or tables from the time they are taken in the transaction and for the remaining duration of the transaction. Locks can be escalated from row level to table level or from page level to table level but never from row level to page level. Each lock requires a little resources, so escalation is a mechanism for sqlserver to avoid “wasting” resources on many small locks and rather opting for one large object lock.
The escalation point to table lock is evaluated and/or attempted first (not necessarily taken) at around 5000 rows and then again after each 1250 locks (approximately). Remember, lock intents count towards this and you will get intent lock on a table before a lock on the page and intent lock on the page before you get locks on any rows, so it is the sum of the locks that are considered. Caveat, there are escalation compatibility constraints to consider and these can prevent lock escalation.
Latches
Latches are held only for the duration of the action. Only one thread can change data on a page at a time. Page latches are held for reading (shared) or updating, inserting, deleting (exclusive) on a page. There can only be one thread per page at any given point in time for any action that changes the data, but there can be multiple readers without compromising data integrity. Hence, you cannot take an exclusive latch or lock on a page that already has a shared latch or shared lock. This is to secure data integrity as the page is the smallest chunk of data possible to manipulate “in isolation” in the datafile.
This also holds true for IAM (Index Allocation Map), GAM (Global Allocation Map). SGAM (Shared Global Allocation Map) and PFS (Page Free Space) pages. These are special pages and are not discussed here, and rather than making a poor attempt at explaining what they are and how they work, please see Paul Randall’s blog or Microsofts own documentation where it is explained in fair detail. This also holds true for tempdb and is relevant for tempdb contention issues and is part of the reason why having several tempdbs helps with database performance. Side note: In recent CUs for some previous versions and from MSSQL 2019 and forward, contention has been a focal point by Microsoft in the development of SQL server, please see this talk that Pam Lahoud had at the 2020 eightkb virtual conference “TempDB: The Good, The Bad and The Ugly” for further details, it is most definitely worth a watch.
To dive further into this there are some good explanations at sqlshack that I have found very informative and easily digested for locks and latches. I recommend having a look.
After digesting the above I also strongly recommend Paul Randal’s article “Knee Jerk Wait Statistics: PAGELATCH”. He also has an amazing wait and latch type library that is very useful for lookup purposes when troubleshooting.
Another helpful resource for “DBAlights” as myself on the topic of waits (the inevitable consequence of, among others, locks and latches) when we are just starting out is the whitepaper “SQL Server Performance Tuning Using Wait Statistics: A Beginner’s Guide” by Jonathan Kehayias and Erin Stellato, published by SQLskills.com.