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.
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 temporary option blocking = 'On'; set temporary option blocking_timeout = '0';
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.
sp_iqlocks
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',,
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.
Commit
delete from rv_locks where c1 = 5;
sp_iqlocks
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.
Rollback
Delete from rv_locks2 where c1 > 0;
sp_iqlocks
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',,