INF: Lock Escalation Does Not Block If a Wait Is Required (159623)

SUMMARY

SQL Server 6.5 and 6.0 Service Pack 3 allow processes to continue requesting page locks if a request to escalate to a table lock is blocked. With earlier versions of SQL Server, applications that had to wait for the escalated lock could have encountered a deadlock situation. This change has considerably reduced the number of deadlocks encountered between the LogReader and Distribution tasks for SQL Server replication, but it can also help out any other application that encounters deadlocks caused by table lock escalation situations.

MORE INFORMATION

In SQL Server 6.0, 6.0 Service Pack 1, and 6.0 Service Pack2, a process would block on a request for an escalation to a table lock if the request for a table lock would cause the process to be blocked. In some situations, this could have resulted in a deadlock situation, and one of the processes would encounter the following error (based on the standard rules for deadlock victim processing):

Msg 1205, "Your server command (process id#%d) was deadlocked with
another process and has been chosen as deadlock victim. Re-run your
command."

In SQL Server 6.0 Service Pack 3 and SQL Server 6.5, the lock manager has been changed to continue requesting page locks if a table lock escalation would be blocked. The server continues to attempt to escalate to a table lock as long as new page locks are needed. This way, a normal lock escalation can occur once the other process frees its locks.

See the SQL documentation in the "Administrator's Companion" for more details about lock types and configuration values to control lock escalations.