Manage Blocking in the RLV Store

The RLV store uses the same transaction blocking mechanism as the IQ main store. Suppose you set the BLOCKING option to ON. If write lock A held be transaction 1 conflicts with write lock B which transaction 2 is attempting to obtain, then transaction 2 must wait until write lock A is released, or until the BLOCKING_TIMEOUT threshold is reached. By default, BLOCKING is OFF.

Although transaction blocking minimizes lock contention, transaction blocking can lead to deadlock.

• Enabling Connection Blocking
  Enable connection blocking to force any transaction attempting to obtain a lock that conflicts with another transaction's existing lock to wait: either until every conflicting lock is released, or until the BLOCKING_TIMEOUT threshold is reached.
• Disabling Connection Blocking
  Disable connection blocking to force any transaction attempting to obtain a lock that conflicts with another transaction's existing lock to roll back the transaction and display an error.
• Setting the Blocking Timeout Threshold
  Use the threshold to set the length of time, in milliseconds, a transaction waits to obtain a lock. If the transaction attempting to obtain a lock conflicts with another transaction's existing lock, it waits until the BLOCKING_TIMEOUT option threshold is reached. If the conflict still exists, the transaction rolls back and you see an error.
• Transaction Blocking Deadlocks
  Transaction blocking can lead to a deadlock situation, in which a set of transactions arrive at a state where none of them can proceed.
• Tutorial: Monitoring Blocking
  In this tutorial, create RLV-enabled tables, execute a transaction, and use the sp_iqtransaction stored procedure to report on connection blocking and blocking timeout information for all transactions in the database.