Lock promotion occurs on a per-scan-session basis.
A scan session is how Adaptive Server tracks scans of tables within a transaction. A single transaction can have more than one scan session for the following reasons:
A table may be scanned more than once inside a single transaction in the case of joins, subqueries, exists clauses, and so on. Each scan of the table is a scan session.
A query executed in parallel scans a table using multiple worker processes. Each worker process has a scan session.
A scan session may scan data from more than one partition. Lock promotion is based on the number of page or row locks acquired across all the partitions accessed in the scan.
A table lock is more efficient than multiple page or row locks when an entire table might eventually be needed. At first, a task acquires page or row locks, then attempts to escalate to a table lock when a scan session acquires more page or row locks than the value set by the lock promotion threshold.
Since lock escalation occurs on a per-scan-session basis, the total number of page or row locks for a single transaction can exceed the lock promotion threshold, as long as no single scan session acquires more than the lock promotion threshold number of locks. Locks may persist throughout a transaction, so a transaction that includes multiple scan sessions can accumulate a large number of locks.
Lock promotion cannot occur if another task holds locks that conflict with the type of table lock needed. For instance, if a task holds any exclusive page locks, no other process can promote to a table lock until the exclusive page locks are released.
When lock promotion is denied due to conflicting locks, a process can accumulate page or row locks in excess of the lock promotion threshold and may exhaust all available locks in Adaptive Server.
The lock promotion parameters are:
For allpages-locked tables and datapages-locked tables, page lock promotion HWM, page lock promotion LWM, and page lock promotion PCT.
For datarows-locked tables, row lock promotion HWM, row lock promotion LWM, and row lock promotion PCT.
The abbreviations in these parameters are:
HWM – high water mark
LWM – ow water mark
PCT – percent
