Sets or changes row-lock promotion thresholds for a datarows-locked table, for all datarows-locked tables in a database, or for all datarows-locked tables on a server.
sp_setrowlockpromote "server", NULL, new_lwm, new_hwm, new_pct
sp_setrowlockpromote {"database" | "table"}, objname, new_lwm,
new_hwm, new_pct
sets server-wide values for the row lock promotion thresholds.
specifies whether to set the row-lock promotion thresholds for a database or table.
is either the name of the table or database for which you are setting the row-lock promotion thresholds or null, if you are setting server-wide values.
specifies the value to set for the low watermark (LWM) threshold. The LWM must be less than or equal to the high watermark (HWM). The minimum value for LWM is 2. This parameter can be null.
specifies the value to set for the high watermark (HWM) threshold. The HWM must be greater than or equal to the LWM. The maximum HWM is 2,147,483,647. This parameter can be null.
specifies the value to set for the lock promotion percentage (PCT) threshold. PCT must be between 1 and 100. This parameter can be null.
Sets row lock promotion values for all datarows-locked tables in the engdb database:
sp_setrowlockpromote "database", engdb, 400, 400,95
Sets row lock promotion values for the sales table:
sp_setrowlockpromote "table", sales, 250, 250, 100
You can display database-level lock promotions using sp_helpdb dbname and table-level locks using sp_helpdb tablename.
sp_setrowlockpromote sets or changes row-lock promotion thresholds for a table, a database, or Adaptive Server.
Adaptive Server acquires row locks on a datarows-locked table until the number of locks exceeds the lock promotion threshold. If Adaptive Server is successful in acquiring a table lock, the row locks are released.
When the number of row locks on a table exceeds the HWM, Adaptive Server attempts to escalate to a table lock. When the number of row locks on a table is below the LWM, Adaptive Server does not attempt to escalate to a table lock. When the number of row locks on a table is between the HWM and LWM, and the number of row locks exceeds the PCT threshold as a percentage of the number of rows in a table, Adaptive Server attempts to escalate to a table lock.
Lock promotion is always two-tiered, that is, row locks are promoted to table locks. Adaptive Server does not promote from row locks to page locks.
Lock promotion thresholds for a table override the database or server-wide settings. Lock promotion thresholds for a database override the server-wide settings.
To change the lock promotion thresholds for a database, you must be using the master database. To change the lock promotion thresholds for a table in a database, you must be using the database where the table resides.
Server-wide row lock promotion thresholds can also be set with sp_configure. When you use sp_setrowlockpromote to change the values server-wide, it changes the configuration parameters, and saves the configuration file. When you first install Adaptive Server, the server-wide row lock promotion thresholds set by the configuration parameters are:
row lock promotion HWM |
200 |
row lock promotion LWM |
200 |
row lock promotion PCT |
100 |
See the System Administration Guide for more information.
The system procedure sp_sysmon reports on row lock promotions.
Database-level row lock promotion thresholds are stored in the master..sysattributes table. If you dump a database, and load it only another server, you must set the row lock promotion thresholds on the new server. Object-level row lock promotion thresholds are stored in the sysattributes table in the user database, and are included in the dump.
Only a system administrator can execute sp_setrowlockpromote.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
System procedures sp_configure, sp_droprowlockpromote, sp_helpdb, sp_sysmon
