Changing to data-only locking may require more locks or may reduce the number of locks required:
Tables using datapages locking require fewer locks than tables using allpages locking, since queries on datapages-locked tables do not acquire separate locks on index pages.
Tables using datarows locking can require a large number of locks. Although no locks are acquired on index pages for datarows-locked tables, data modification commands that affect many rows may hold more locks.
Queries running at transaction isolation level 2 or 3 can acquire and hold very large numbers of row locks.
An insert with allpages locking requires N+1 locks, where N is the number of indexes. The same insert on a data-only-locked table locks only the data page or data row.
Scans at transaction isolation level 1, with read committed with lock set to hold locks (1), acquire overlapping locks that roll through the rows or pages, so they hold, at most, two data page locks at a time.
However, transaction isolation level 2 and 3 scans, especially those using datarows locking, can acquire and hold very large numbers of locks, especially when running in parallel. Using datarows locking, and assuming no blocking during lock promotion, the maximum number of locks that might be required for a single table scan is:
row lock promotion HWM * parallel_degree
If lock contention from exclusive locks prevents scans from promoting to a table lock, the scans can acquire a very large number of locks.
Instead of configuring the number of locks to meet the extremely high locking demands for queries at isolation level 2 or 3, consider changing applications that affect large numbers of rows to use the lock table command. This command acquires a table lock without attempting to acquire individual page locks.
See “lock table Command” for information on using lock table.
For tables that use the datarows locking scheme, data modification commands can require many more locks than data modification on allpages or datapages-locked tables.
For example, a transaction that performs a large number of inserts into a heap table may acquire only a few page locks for an allpages-locked table, but requires one lock for each inserted row in a datarows-locked table. Similarly, transactions that update or delete large numbers of rows may acquire many more locks with datarows locking.
