“Lock Detail” provides information that you can use to determine whether the application is causing a lock contention or deadlock-related problem.
This output reports locks by type, displaying the number of times that each lock type was immediately granted and the number of times a task had to wait for a particular type of lock. The “% of total” is the percentage of the specific lock type that was granted or had to wait with respect to the total number of lock requests.
“Lock Detail” reports these types of locks:
Exclusive Table
Shared Table
Exclusive Intent
Shared Intent
Exclusive Page
Update Page
Shared Page
Exclusive Row
Update Row
Shared Row
Exclusive Address
Shared Address
Last Page Locks on Heaps
Lock contention can have a large impact on Adaptive Server performance. Table locks generate more lock contention than page or row locks because no other tasks can access a table while there is an exclusive table lock on it, and, if a task requires an exclusive table lock, it must wait until all shared locks are released. If lock contention is high, run sp_object_stats to help pinpoint the tables involved.
“Avg Hash Chain Length” reports the average number of locks per hash bucket during the sample interval. You can configure the size of the lock hash table with the configuration parameter lock hashtable size. If the average number of locks per hash chain is more than four, consider increasing the size of the hash table.
Large inserts with bulk copy are an exception to this guideline. Lock hash chain lengths may be longer during large bulk copies.
See Chapter 2, “Locking Configuration and Tuning,” in Performance and Tuning Series: Locking and Concurrency Control for more information.
For more information about configuration parameters, see Chapter 5, “Setting Configuration Parameters,” in the System Administration Guide, Volume 1.
