A write-intent lock is a table write lock that grants the transaction permission to write to a table row in the future. A write-intent lock can be held by multiple requesting connections.
A write intent lock always exists when the RLV-enabled portion of the table exists in memory. You can view details of the write intent lock using the sp_iqlocks stored procedure.
The write-intent lock conflicts with table write locks and table exclusive locks. This conflict prevents a table-level snapshot-versioned transaction from writing to the table or performing a DDL operation until the lock manager releases all write-intent locks on the table. In a situation where both table-level snapshot-versioned transactions and row-level snapshot-versioned transaction connections write to a table, write-intent locks provide synchronization. Consider this scenario:
Connection | Action |
---|---|
Row-level snapshot-versioned transaction A |
|
Table-level snapshot-versioned transaction B | Attempts to write to table_1. Transaction B blocked by write intent lock. |
Row-level snapshot-versioned transaction A | Commits transaction A. Table changes are merged from the RLV store to the IQ main store. Write-intent locks released. |
Table-level snapshot-versioned transaction B | Proceeds with write to table_1. |