This can be explained in detail as follows:
- T1 hold an IX lock on Page P1.
- T2 do a page split on P1, allocate a new page P2, a system transaction TX is used here, it holds an X lock on P2. Here SQL Server did not put the hobt_id in Mirroring Log.
- TX does a lock migration for T1 to move the IX lock from P1 to P2.
- TX committed, now T2 can use Page P2, and T2 obtain another IX lock on page P2.
- T1 committed, now T2 is the only one who holds an IX lock on P2.
- After lots of inserting, a lock escalation occurs, on the primary, T2 releases the IX on P2, but on the mirror, during lock escalation, T2 did not release the IX lock.
- After lots of deleting, Page P2 became empty and is deallocated.
- T3 needs a new page, and it happens to allocate P2, this requires an X lock, but on the mirror, this step failed because of step 6.
On the mirror, Step 6 does not release the IX lock because the hobt_id in the lock block is incorrect. This incorrect hobt_id comes during step 2 and because of SQL Server does not put the hobt_id in Mirroring Log.
Usually you do not see any issue because the TX in step 2 is very short, and the lock block with incorrect hobt_id will be released when it commits. However, because of lock migration in step3 and the following steps (4 and 5), this lock block with incorrect hobt_id is preserved and finally causes the problem.
The primary does not have this issue because it uses a correct hobt_id in step 2. But the log record does not have correct hobt_id.
Article ID: 2938828 - Last Review: 24 Apr 2014 - Revision: 1