Books Online has a good article about this, which explains a lot of the details about how many locks you need to take out to trigger lock escalation. Here are the (simplified) basics:
-
The ‘magic’ number to trigger escalation for the first time is 5,000 locks on a single table reference
-
Locks do NOT escalate from row level to page level. Row locks escalate to table. Page level locks also escalate to table level. In other words, forcing row level locking will not make it less likely to escalate locks to the table level, but rather it will do the opposite.
- Note: for partitioned tables, you have the option to enable partition level escalation
-
If you’re modifying data, the escalated table lock will be exclusive. That means nobody else can party with the table if lock escalation succeeds while you’re doing your work.
Not all locks count, though, and Kendra has provided a test to show this.