row lock promotion HWM

row lock promotion HWM (high-water mark), with row lock promotion LWM (low-water mark) and row lock promotion PCT specifies the maximum number of row locks permitted during a single scan session of a table or an index before SAP ASE attempts to escalate from row locks to a table lock.

Summary Information

Default value

200

Range of values

2–2147483647

Status

Dynamic

Display level

Intermediate

Required role

System administrator

Configuration groups

Lock Manager, SQL Server Administration

When the number of locks acquired during a scan session exceeds row lock promotion HWM, SAP ASE attempts to acquire a table lock. The lock promotion HWM value cannot be higher than the number of locks value.

See Performance and Tuning Series: Locking and Concurrency Control > Locking Configuration and Tuning.

The default value for row lock promotion HWM is appropriate for most applications. To avoid table locking, you may want to increase the value of row lock promotion HWM.. For example, if you know that there are regular updates to 500 rows on a table that has thousands of rows, you can increase concurrency for the tables by setting row lock promotion HWM to around 500.

You can also configure row lock promotion at the object level. See sp_setpglockpromote in the Reference Manual: Procedures.