SQL Server Back to Basics with this "SQL Server: Locking basics" post

Vinod Kumar (Blog home) - SQL Server: Locking basics

"The learning journey can never stop. As we learn the basic building blocks of SQL Server there is always a need to do refreshers. This blog is on the same lines. I would urge you to read the Blog around Concurrency over at Pinal’s site and then continue the reading below because the basics of Locking is incomplete if we don’t understand what are the concurrency basics. Just like you have traffic signals that regulates traffic to make sure there are no grid locks or long waits for commuters. The parallel lanes for traffic is synonymous to concurrency. SQL Server uses locks as an mechanism to bring the consistency at the same time build the concurrency for the system. Locks are inevitable in any database world and those are also fundamental building blocks.

1. Locks are applied in both optimistic and pessimistic concurrency models.
2. Locking is used for maintaining consistency for concurrent transactions.
3. Read operations acquire shared locks, exclusive locks by write operations while update locks are got during initial portion of an update operation when SQL Server is searching for the data to update.
4. SQL Server releases locks automatically and manages compatibility between various locking modes, resolves deadlocks and lock escalations are done to reduce overheads.
5. SQL Server controls locks on tables, on the pages of the table, on index keys and on individual rows of data based on the operation done

...

..."

You think you know something, then you read something and realize you don't know anything (sigh... that's like the story of my life...). While I've worked with SQL Server since 4.21a, there's still stuff I learned reading this post.