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 |
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 Adaptive Server attempts to escalate from row locks to a table lock.
When the number of locks acquired during a scan session exceeds row lock promotion HWM, Adaptive Server attempts to acquire a table lock. The lock promotion HWM value cannot be higher than the number of locks value.
See Chapter 2, “Locking Configuration and Tuning,” in Performance and Tuning Series: Locking and Concurrency Control.
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.
