SQL Server lets you manage multiple users simultaneously and ensure that transactions observe the properties of the chosen isolation level. Locking guards data and the internal resources that make it possible for a multiple- user system to operate like a single-user system. In this chapter, we looked at the locking mechanisms in SQL Server, including full locking for data and leaf-level index pages and lightweight locking mechanisms for internally used resources.
We also looked at the types and modes of locks as well as lock compatibility and lock escalation. It is important to understand the issues of lock compatibility if you want to design and implement high-concurrency applications. We also looked at deadlocks and discussed ways to avoid them.