Tutorial: Monitoring Row-Level Locks

In this tutorial, create RLV-enabled tables, commit a transaction, and delete the committed row to show row locking, and row-range locking. The sp_iqlocks stored procedure reports on the row-level locks.

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

4. Execute sp_iqlocks.

   sp_iqlocks

   A write-intent lock displays.

   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. Shared schema locks prevent other transactions from performing DML actions against the table.

5. Commit the transaction.

   Commit

   During the commit, the database releases the locks held by the transaction. In this example, 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.
6. Delete the row that was previously committed.

   delete from rv_locks where c1 = 5;

   Before deleting the row, the database takes-out a row-level lock.
7. Execute sp_iqlocks again.

   sp_iqlocks

   Three locks display: a shared lock, a row lock, and 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_locks2',,'Schema','Transaction','Shared',,
   'SQL_DBC_13cd6038',3,'DBA','BASE','DBA','rv_locks2',,'Row','Transaction','Row',281474976710656,1
   'RVL_CONN_T775',1000000407,'','BASE','DBA','rv_locks2',,'Table','Transaction','Intent',,
   

   Row 1 shows a lock type of Shared, indicating a shared schema lock. This lock is held by the DML for the DELETE statement. The shared schema lock prevents other transactions from performing DDL actions against the table.

   Row 2 shows a lock type of Row, indicating a row-level lock:

   • row_identifier – 281474976710656 is the row identifier of the row the lock starts on.
   • row_range – 1 indicates that a single row was locked.
8. Delete a range of rows to illustrate row-range locking.
   1. Roll back the current transaction which is performing the delete, where c1=5.

      Rollback

   2. Delete all rows where c1>0.

      Delete from rv_locks2 where c1 > 0;

9. Execute sp_iqlocks again.

   sp_iqlocks

   Four locks display: a shared lock, two row locks (one with a row_range value), and 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_locks2',,'Schema','Transaction','Shared',,
   'SQL_DBC_13cd6038',3,'DBA','BASE','DBA','rv_locks2',,'Row','Transaction','Row',1,4
   'SQL_DBC_13cd6038',3,'DBA','BASE','DBA','rv_locks2',,'Row','Transaction','Row',281474976710656,1
   'RVL_CONN_T775',1000000407,'','BASE','DBA','rv_locks2',,'Table','Transaction','Intent',,
   

   Note: The second output row represents rows locked from the table-level version, whereas the third output row represents the rows locked from the row-level version.