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 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, low water mark
PCT, percent
