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.

Prerequisites

SAP Sybase IQ server has a simplex database.
RLV storage is configured.

Task

Create RLV-enabled tables rv_locks and rv_locks2, and configure table-level snapshot versioning:

SET TEMPORARY OPTION SNAPSHOT_VERSIONING = 'Table-level';

CREATE TABLE rv_locks(c1 INT, c2 INT, c3 INT);

INSERT INTO rv_locks VALUES (1,1,1);
INSERT INTO rv_locks VALUES (2,2,2);
INSERT INTO rv_locks VALUES (3,3,3);
INSERT INTO rv_locks VALUES (4,4,4);
COMMIT;

CREATE TABLE rv_locks2(c1 int, c2 int, c3 int);

INSERT INTO rv_locks2 VALUES (1,1,1);
INSERT INTO rv_locks2 VALUES (2,2,2);
INSERT INTO rv_locks2 VALUES (3,3,3);
INSERT INTO rv_locks2 VALUES (4,4,4);
COMMIT;

ALTER TABLE rv_locks ENABLE RLV STORE;
ALTER TABLE rv_locks2 ENABLE RLV STORE;

Set the snapshot versioning property of the transaction to row-level.
```
set temporary option Snapshot_Versioning = 'Row-level';
```

Enable connection blocking and set the blocking timeout threshold.

set temporary option blocking = 'On';
set temporary option blocking_timeout = '0';

Write to the table.
```
insert into rv_locks values (5,5,5);
```
Writing to, or querying, an RLV-enabled table creates the RLV-enabled portion of the table in-memory, on-demand.
Execute sp_iqtransaction to view information for all transactions in the database.
```
sp_iqtransaction
```
Transaction information displays, with each row representing a different transaction:
```
Name,Userid,TxnID,CmtID,VersionID,State,ConnHandle,IQConnID,MainTableKBCr,MainTableKBDr,TempTableKBCr,TempTableKBDr,TempWorkSpaceKB,TxnCreateTime,CursorCount,SpCount,SpNumber,MPXServerName,GlobalTxnID,VersioningType,Blocking,BlockingTimeout
'SQL_DBC_13cd6038','DBA',1008,0,0,'ACTIVE',3,92,0,0,0,0,0,'2012-08-08 09:00:39.511',0,4,36,,0,'Row-level','True',0
```
The Blocking value is True, meaning that connection blocking is enabled using the BLOCKING database option. Connection blocking means that when lock contention is detected, the transaction waits (or blocks) for the conflicting lock to release before requesting the lock again.

The Blocking_Timeout value is 0 (the default value), meaning the transaction will wait indefinitely for the conflicting lock to release.