Estimating number of locks for data-only-locked tables

Changing to data-only locking may require more locks or may reduce the number of locks required:

Insert commands and 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.

select queries and locks

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.

Data modification commands and locks

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.