Two locks are compatible if one lock can be granted while another lock on the same object by a different process is outstanding. On the other hand, if a lock requested for an object is not compatible with a lock currently being held, the requesting connection must wait for the lock. For example, if a shared page lock exists on a page, another process requesting a shared page lock for the same page is granted the lock because the two lock types are compatible. But a process that requests an exclusive lock for the same page is not granted the lock because an exclusive lock is not compatible with the shared lock already held. Table 13-3 summarizes the compatibility of locks in SQL Server.
Lock compatibility comes into play between locks on different resources, such as table locks and page locks. A table and a page obviously represent an implicit hierarchy since a table is made up of multiple pages. If an exclusive page lock is held on one page of a table, another process cannot get even a shared table lock for that table. This hierarchy is protected using intent locks. A process acquiring an exclusive page lock, update page lock, or intent exclusive page lock first acquires an intent exclusive lock on the table. This intent exclusive table lock prevents another process from acquiring the shared table lock on that table. (Remember that intent exclusive and shared locks are not compatible.)
Table 13-3. Yes indicates that the lock held is compatible with the lock requested; No means that they're not compatible.
|Requested Mode||Column Span|
Similarly, a process acquiring a shared row lock must first acquire an intent shared lock for the table, which prevents another process from acquiring an exclusive table lock. Or if the exclusive table lock already exists, the intent shared lock is not granted and the shared page lock has to wait until the exclusive table lock is released. Without intent locks, process A can lock a page in a table with an exclusive page lock and process B can place an exclusive table lock on the same table and hence think that it has a right to modify the entire table, including the page that process A has exclusively locked.
At the risk of stating the obvious, lock compatibility is an issue only when the locks affect the same object. For example, two or more processes can each hold exclusive page locks simultaneously as long as the locks are on different pages or different tables.
Even if two locks are compatible, the requester of the second lock might still have to wait if an incompatible lock is waiting. For example, suppose that process A holds a shared page lock. Process B requests an exclusive page lock and must wait because the shared page lock and the exclusive page lock are not compatible. Process C requests a shared page lock that is compatible with the shared page already outstanding to process A. However, the shared page lock cannot be immediately granted. Process C must wait for its shared page lock because process B is ahead of it in the lock queue with a request (exclusive page) that is not compatible.
By examining the compatibility of locks not only to those processes granted but also to those processes waiting, SQL Server prevents lock starvation , which occurs when requests for shared locks keep overlapping so that the request for the exclusive lock can never be granted.