SQL BOL: "Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead."
In theory, SQL Server escalates rowlocks and page locks into table locks when transactions exceed a dynamically determined escalation threshold. This threshold is not user configurable.
In practice, we've all seen SQL Server transactions that acquire hundreds or even thousands of page locks during an update transaction without ever seeming to escalate to table locking. If it is known that a transaction will update a large percentage of the rows in a table, we can use hints to escalate to table locking immediately. This will eliminate the need for SQL Server to acquire row or page locks and thus speed processing. The following example shows how to force table locking during an update transaction.
--------------------- Begin Transaction
Select count(*) From MyTblA With (tablockx holdlock) Where 1=2 /* tablockx forces an exclusive table lock Use tablock to do a non-exclusive lock holdlock tells SQL Server to hold locks for duration of the transaction */
Update MyTblA Set Col1 = b.Col3 From MyTblA a Inner Join MyTblB b On a.ID=b.ID Where a.Col1 != b.Col3
Commit Transaction ---------------------
This technique should be used judiciously. Locking a table unnecessarily could have detrimental impact on other processes.
It has been noted that the word WITH is optional and actually caused syntax errors in SQL Server version 6.5.
SQL Books Online for version 7.0 indicate that WITH is optional but encouraged. The following quote is from SQL BOL version 7.0.
The use of the WITH keyword is encouraged although it is not currently required. In future releases of SQL Server, WITH may be a required keyword.
I also found the following in BOL.
The use of commas between table hints is optional but encouraged. Separation of hints by spaces rather than commas is supported for backward compatibility.