The Lock Manager

The responsibility for ensuring lock conflict resolution between user processes falls on the SQL Server Lock Manager. SQL Server automatically assigns locks to processes to guarantee that the current user of a resource (a data page, index page, table, index, database, and so on) has a consistent view of that resource from beginning to end of a particular operation. In other words, what you start with will be what you work with throughout your operation. Nobody can change what you are working on in mid-state, thereby ensuring the consistency of your transaction.

The Lock Manager is responsible for deciding the appropriate lock type (shared, exclusive, update, and so on) and the appropriate granularity of locks (row, page, table, and so on) according to the type of operation being performed and the amount of data being affected. Based on the type of transaction, the SQL Server Lock Manager chooses different types of lock resources. For example, a CREATE INDEX statement might lock the entire table, whereas an UPDATE statement might lock only a specific row.

The Lock Manager also manages compatibility between lock types attempting to access the same resources, resolves deadlocks, and escalates locks to a higher level if necessary.

The Lock Manager manages locks for both shared data and for internal system resources. For shared data, the Lock Manager manages row locks, page locks, and table locks on tables, as well as data pages, text pages, and leaf level index pages. Internally, the Lock Manager uses latches to manage locking on index rows and pages, controlling access to internal data structures, and in some cases, for retrieving individual rows of data. Latches provide better system performance because they are less resource intensive than locks. Latches also provide greater concurrency than locks. Latches are typically used for operations such as page splits , deletion of index rows, movement of rows in an index, and so on. The main difference between a lock and a latch is that a lock is held for the duration of the transaction, and a latch is held only for the duration of the operation for which it is required. Locks are used to ensure the logical consistensy of the data, whereas latches are used to ensure the physyical consistency of the data and the data structures.

The remainder of this chapter will examine how the Lock Manager determines the type and level of lock to assign based on the type of command being executed, the number of rows affected, and the lock isolation level in effect at the time.