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.

1. 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;
   

2. Enable connection blocking and set the blocking timeout threshold:

   SET TEMPORARY OPTION BLOCKING = 'ON';
   SET TEMPORARY OPTION BLOCKING_TIMEOUT = '0';

3. Use the sp_iqlocks stored procedure to view the current set of database locks. At this point, no locks are returned.

   sp_iqlocks

   The absence of a write-intent lock for the RLV-enabled table indicates that the in-memory RLV portion of the table has yet to be created. 
4. Set the snapshot versioning property of the transaction to row-level.

   SET TEMPORARY OPTION SNAPSHOT_VERSIONING = 'row-level';

5. 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.

6. Re-execute sp_iqlocks.

   sp_iqlocks

   This time, the procedure returns a write-intent lock.

   conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier,row_range
   'SQL_DBC_13cd6038',3,'DBA','BASE','DBA','rv_locks',,'Schema','Transaction','Shared',,
   'RLV_CONN_T775',1000000407,'','BASE','DBA','rv_locks',,'Table','Transaction','Intent',,
   

   Connection ID 100000407 has a write-intent lock on the rv_locks table. The lock type is set to Intent, which indicates a write intent lock.

   Note: The connection ID number (100000407) is large because it represents an internal connection within the server itself. This internal connection is used to manage locks on the RLV-enabled table.

   ConnectionID 3 has a schema lock on the table. The lock type is set to Shared, which indicates a shared schema lock

7. Return to the uncommitted transaction that performed the insert, and commit it:

   Commit

   During the commit, the database releases the locks held by the transaction. For the tutorial, this releases only the shared schema lock. The RLV-enabled table now exists in memory, with committed data. Therefore, the only lock present at this point is the write-intent lock held by the RLV-enabled portion of the table.
8. Re-execute sp_iqlocks.

   sp_iqlocks

   The schema lock is gone, but the write-intent lock remains:

   conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type,row_identifier,row_range
   'RVL_CONN_T775',1000000407,'','BASE','DBA','rv_locks2',,'Table','Transaction','Intent',,
   

   Note: The row for conn_id 100000407 has not changed since the last time you executed sp_iqlocks.
9. Execute sp_iqconnection to view connection details

   sp_iqconnection

   You see:

   ConnHandle,Name,Userid,LastReqTime,ReqType,IQCmdType,LastIQCmdTime,IQCursors,LowestIQCursorState,IQthreads,TxnID,ConnCreateTime,TempTableSpaceKB,TempWorkSpaceKB,IQconnID,satoiq_count,iqtosa_count,CommLink,NodeAddr,LastIdle,MPXServerName,LSName,INCConnName,INCConnSuspended
   1,'SQL_DBC_13de5fd8','DBA','2012-08-08 08:49:25.629','PREFETCH','NONE',2012-08-08 08:49:25.0,0,'NONE',0,0,2012-08-08 08:49:24.0,0,0,70,40,2,'local','',0,,,'','N'
   3,'SQL_DBC_13cd6038','DBA','2012-08-08 09:25:32.920','OPEN','IQUTILITYOPENCURSOR',2012-08-08 09:25:32.0,0,'NONE',0,1008,2012-08-08 08:50:04.0,0,0,92,187,413,'local','',8789,,,'','N'
   1000000407,'INT: RLVLockConn','','','unknown (0)','NONE',0001-01-01 00:00:00.0,0,'NONE',0,0,2012-08-08 09:00:40.0,0,0,410,2,0,'NA','NA',0,,,'','N'
   

   The third row (ConnHandle 1000000407) provides information on the internal connection (RLVLockConn) used by the RLV-enabled table to control the write-intent lock.

   Note: ConnHandle 1000000407 matches conn_id 100000407 in sp_iqlocks output. It also matches ConnHandle 1000000407 in sp_iqtransaction output.
10. Execute sa_conn_info to view additional connection details. sa_conn_info is similar to sp_iqconnection.

    sa_conn_info

    You see:

    Number,Name,Userid,DBNumber,LastReqTime,ReqType,CommLink,NodeAddr,ClientPort,ServerPort,BlockedOn,LockRowID,LockIndexID,LockTable,UncommitOps,ParentConnection
    1000000407,sa_'INT: RLVLockConn','',0,'','unknown (0)','NA','NA',0,0,0,0,,'',0,
    3,'SQL_DBC_13cd6038','DBA',0,'2012-08-08 09:30:43.799','FETCH','local','',0,0,0,0,,'',0,
    1,'SQL_DBC_13de5fd8','DBA',0,'2012-08-08 08:49:25.629','PREFETCH','local','',0,0,0,0,,'',0,
    

    Note: In the first row, Number 1000000407 matches ConnHandle 1000000407 in the sp_iqconnection output, and conn_id 100000407 in the sp_iqlocks output.

    Userid "INT: RLVLockConn" indicates an internal connection. This connection is used by the RLV-enabled table to control the write-intent lock.

• Row Locks
  A row lock is a table-row write lock that allows the holding transaction to write to any column of a locked row. Only one holder of this lock can exist at a time. A write-intent lock is a prerequisite; the transaction must hold a write-intent lock before the lock manager grants it a row lock.