Row-Level Snapshot Versioning

Row-level snapshot versioning applies only to tables enabled for in-memory RLV storage. Row-level snapshot versioning allows multiple writers to make concurrent DML changes to a table, but never to the same rows at the same time.

Row-level snapshot versioning locks the table at the row level using row locks. A row lock provides a write lock for a table row, meaning the transaction gets blocked, or fails, depending on the BLOCKING and BLOCKING_TIMEOUT option settings. If BLOCKING is ON, the transaction blocks. If BLOCKING is OFF, the transaction fails immediately with an ALREADY LOCKED SQL exception.

Transaction blocking enables row-level snapshot versioning to write to different rows of the same table simultaneously. Depending on the BLOCKING and BLOCKING_TIMEOUT option settings, row-lock contention results either in an error, or a retry to obtain the lock if it is released within the specified timeout period. When a transaction configured for table-level versioning attempts to write to a table with a row locked by a row-level versioned transaction, the table-level transaction either fails with an error, or blocks and retries if the lock is released within the specified timeout period.

DDL changes to a table (CREATE, DROP, and ALTER), however, lock the table at the table level.

• Specifying Snapshot Versioning
  Use the SNAPSHOT_VERSIONING option to set the snapshot versioning type to either Row-level or Table-Level. You can set the option at the database (PUBLIC) level, connection level (TEMPORARY) or user level. To use the in-memory RLV store, enable row-level snapshot versioning for your transactions. For simultaneous updates to different rows of the same table, each transaction or connection must also enable row-level snapshot versioning.
• Tutorial: Monitoring Write-Intent Locks
  In this tutorial, create RLV-enabled tables, execute a transaction, and use the sp_iqlocks stored procedure to report on schema-level locks and write-intent locks in the database. Then use the sp_iqconnection and sa_conn_info stored procedures to view the internal connection controlling the write-intent lock.
• Monitoring Write Intent Locks in Interactive SQL
  View details on write intent locks using the sa_locks system procedure.
• Row-Level DDL Locking Considerations
  Data Definition Language (DDL) changes (for example, CREATE INDEX, DROP INDEX, and ALTER TABLE ADD, ALTER, or DROP) to an RLV-enabled table require an exclusive table-level lock. For DDL events, the locking behavior for an RLV-enabled table is the same as for an IQ main store table: the writing connection has an exclusive lock on the table. When BLOCKING is set to ON, all competing DML and DDL transactions against the table are blocked until the DDL changes are committed. When BLOCKING is set to OFF, the competing transaction will immediately fail the lock request.