Summary information |
|
|---|---|
Default value |
200 |
Range of values |
2–2147483647 |
Status |
Dynamic |
Display level |
Intermediate |
Required role |
System Administrator |
row lock promotion HWM (high-water mark), together with row lock promotion LWM (low-water mark) and row lock promotion PCT specifies the 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.
row lock promotion HWM sets a maximum number of row locks allowed on a table before Adaptive Server attempts to escalate 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.
For more information on scan sessions and setting up lock promotion limits, see “Configuring locks and lock promotion thresholds” in Performance and Tuning: Locking.
The default value for row lock promotion HWM is appropriate for most applications. You might want to raise the value to avoid table locking. 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 per-object level. See sp_setpglockpromote in the Reference Manual.
