“Unpinning” occurs when two transactions attempt to update the same data page, causing the second transaction to flush the first transaction’s user log cache, unpinning the data page from the first transaction’s ULC.
Adaptive Server typically uses row-level locking for increased concurrency, since both transactions are trying to update the same page.
Unpinning occurs when:
Transaction T1 updates row R1.
Adaptive Server logs the update in P1’s ULC, ensuring that this data page is pinned to T1’s ULC.
When T2 tries to update a different row on page P1, it finds that this page is pinned to T1’s ULC.
To update the page, T2 flushes T1’s ULC, unpinning the data page.
To reduce the number of unpins:
If possible, redesign the application so that it directly requests different data pages.
If row locking is inevitable, spread the data to more data pages with sp_chgattribute max_rows_per_page.
