“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 granted immediately, 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 the following 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.
See “Identifying tables where concurrency is a problem” on page 88 in Performance and Tuning: Locking for more information.
