ij> -- -- Licensed to the Apache Software Foundation (ASF) under one or more -- contributor license agreements. See the NOTICE file distributed with -- this work for additional information regarding copyright ownership. -- The ASF licenses this file to You under the Apache License, Version 2.0 -- (the "License"); you may not use this file except in compliance with -- the License. You may obtain a copy of the License at -- -- http://www.apache.org/licenses/LICENSE-2.0 -- -- Unless required by applicable law or agreed to in writing, software -- distributed under the License is distributed on an "AS IS" BASIS, -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. -- See the License for the specific language governing permissions and -- limitations under the License. -- -- test DDL Table Lock mode call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 2000; ij> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA; 0 rows inserted/updated/deleted ij> -- create tables with different lock modes drop table default1; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'DEFAULT1' because it does not exist. ij> create table default1(c1 int); 0 rows inserted/updated/deleted ij> drop table row1; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'ROW1' because it does not exist. ij> create table row1(c1 int); 0 rows inserted/updated/deleted ij> alter table row1 locksize row; 0 rows inserted/updated/deleted ij> drop table table1; ERROR 42Y55: 'DROP TABLE' cannot be performed on 'TABLE1' because it does not exist. ij> create table table1(c1 int); 0 rows inserted/updated/deleted ij> alter table table1 locksize table; 0 rows inserted/updated/deleted ij> -- verify that views have table lock mode of 'R' (ignored) create view v1 as select * from table1; 0 rows inserted/updated/deleted ij> select tablename, lockgranularity from sys.systables where tablename = 'V1'; TABLENAME |& ---------------------------------------------------------------------------------------------------------------------------------- V1 |R ij> drop view v1; 0 rows inserted/updated/deleted ij> -- verify that system tables have lock mode of 'R' select tablename, lockgranularity from sys.systables where tablename = 'SYSTABLES'; TABLENAME |& ---------------------------------------------------------------------------------------------------------------------------------- SYSTABLES |R ij> -- READ COMMITTED tests call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); 0 rows inserted/updated/deleted ij> maximumdisplaywidth 2000; ij> set current isolation = CS; 0 rows inserted/updated/deleted ij> -- all selects should be row locked except for table1 select * from default1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all selects should be row locked except for table1 select * from default1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for DEFAULT1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from row1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from row1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for ROW1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from table1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from table1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for TABLE1 at read committed isolation level using instantaneous share table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> -- scans for all updates should be row locked except for table1 update default1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- scans for all updates should be row locked except for table1 update default1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update default1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update default1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> update row1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update row1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update row1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update row1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> update table1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update table1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update table1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update table1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- all inserts should be row locked except for table1 insert into default1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all inserts should be row locked except for table1 insert into default1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using row locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> insert into row1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into row1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using row locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> insert into table1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into table1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using table locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> -- scans for all deletes should be row locked except for table1 delete from default1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- scans for all deletes should be row locked except for table1 delete from default1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from default1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from default1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from row1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from row1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from row1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from row1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from table1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from table1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from table1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from table1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> -- REPEATABLE READ tests -- repeatable read works the same as serializable when no indexes are involved -- create tables with different lock modes drop table default1; 0 rows inserted/updated/deleted ij> create table default1(c1 int); 0 rows inserted/updated/deleted ij> drop table row1; 0 rows inserted/updated/deleted ij> create table row1(c1 int); 0 rows inserted/updated/deleted ij> alter table row1 locksize row; 0 rows inserted/updated/deleted ij> drop table table1; 0 rows inserted/updated/deleted ij> create table table1(c1 int); 0 rows inserted/updated/deleted ij> alter table table1 locksize table; 0 rows inserted/updated/deleted ij> set current isolation RS; 0 rows inserted/updated/deleted ij> -- all selects should be row locked except for table1 select * from default1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all selects should be row locked except for table1 select * from default1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for DEFAULT1 at repeatable read isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from row1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from row1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for ROW1 at repeatable read isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from table1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from table1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for TABLE1 at repeatable read isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> -- scans for all updates should be row locked except for table1 update default1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- scans for all updates should be row locked except for table1 update default1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at repeatable read isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update default1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update default1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at repeatable read isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> update row1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update row1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at repeatable read isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update row1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update row1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at repeatable read isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> update table1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update table1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at repeatable read isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update table1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update table1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at repeatable read isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- all inserts should be row locked except for table1 insert into default1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all inserts should be row locked except for table1 insert into default1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using row locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> insert into row1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into row1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using row locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> insert into table1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into table1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using table locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> -- scans for all deletes should be row locked except for table1 delete from default1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- scans for all deletes should be row locked except for table1 delete from default1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at repeatable read isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from default1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from default1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at repeatable read isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from row1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from row1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at repeatable read isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from row1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from row1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at repeatable read isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from table1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from table1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at repeatable read isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from table1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from table1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at repeatable read isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> -- alter table -- first set to same value (stupid test) alter table default1 locksize row; 0 rows inserted/updated/deleted ij> alter table row1 locksize row; 0 rows inserted/updated/deleted ij> alter table table1 locksize table; 0 rows inserted/updated/deleted ij> select tablename, lockGranularity from sys.systables where tablename in ('DEFAULT1', 'ROW1', 'TABLE1') order by tablename; TABLENAME |& ---------------------------------------------------------------------------------------------------------------------------------- DEFAULT1 |R ROW1 |R TABLE1 |T ij> -- set to opposite value alter table default1 locksize table; 0 rows inserted/updated/deleted ij> alter table row1 locksize table; 0 rows inserted/updated/deleted ij> alter table table1 locksize row; 0 rows inserted/updated/deleted ij> select tablename, lockGranularity from sys.systables where tablename in ('DEFAULT1', 'ROW1', 'TABLE1') order by tablename; TABLENAME |& ---------------------------------------------------------------------------------------------------------------------------------- DEFAULT1 |T ROW1 |T TABLE1 |R ij> -- READ UNCOMMITTED tests -- create tables with different lock modes drop table default1; 0 rows inserted/updated/deleted ij> create table default1(c1 int); 0 rows inserted/updated/deleted ij> drop table row1; 0 rows inserted/updated/deleted ij> create table row1(c1 int); 0 rows inserted/updated/deleted ij> alter table row1 locksize row; 0 rows inserted/updated/deleted ij> drop table table1; 0 rows inserted/updated/deleted ij> create table table1(c1 int); 0 rows inserted/updated/deleted ij> alter table table1 locksize table; 0 rows inserted/updated/deleted ij> set isolation = read uncommitted; 0 rows inserted/updated/deleted ij> -- all selects should be row locked except for table1 select * from default1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all selects should be row locked except for table1 select * from default1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for DEFAULT1 at read uncommitted isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from row1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from row1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for ROW1 at read uncommitted isolation level using share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from table1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from table1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for TABLE1 at read uncommitted isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> -- scans for all updates should be row locked except for table1 update default1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- scans for all updates should be row locked except for table1 update default1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read uncommitted isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update default1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update default1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read uncommitted isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> update row1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update row1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read uncommitted isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update row1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update row1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read uncommitted isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> update table1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update table1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read uncommitted isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update table1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update table1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read uncommitted isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- all inserts should be row locked except for table1 insert into default1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all inserts should be row locked except for table1 insert into default1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using row locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> insert into row1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into row1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using row locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> insert into table1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into table1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using table locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> -- scans for all deletes should be row locked except for table1 delete from default1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- scans for all deletes should be row locked except for table1 delete from default1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read uncommitted isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from default1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from default1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read uncommitted isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from row1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from row1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read uncommitted isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from row1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from row1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read uncommitted isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from table1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from table1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read uncommitted isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from table1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from table1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read uncommitted isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> -- alter table -- first set to same value (stupid test) alter table default1 locksize row; 0 rows inserted/updated/deleted ij> alter table row1 locksize row; 0 rows inserted/updated/deleted ij> alter table table1 locksize table; 0 rows inserted/updated/deleted ij> select tablename, lockGranularity from sys.systables where tablename in ('DEFAULT1', 'ROW1', 'TABLE1') order by tablename; TABLENAME |& ---------------------------------------------------------------------------------------------------------------------------------- DEFAULT1 |R ROW1 |R TABLE1 |T ij> -- set to opposite value alter table default1 locksize table; 0 rows inserted/updated/deleted ij> alter table row1 locksize table; 0 rows inserted/updated/deleted ij> alter table table1 locksize row; 0 rows inserted/updated/deleted ij> select tablename, lockGranularity from sys.systables where tablename in ('DEFAULT1', 'ROW1', 'TABLE1') order by tablename; TABLENAME |& ---------------------------------------------------------------------------------------------------------------------------------- DEFAULT1 |T ROW1 |T TABLE1 |R ij> -- SERIALIZABLE tests -- create tables with different lock modes drop table default1; 0 rows inserted/updated/deleted ij> create table default1(c1 int); 0 rows inserted/updated/deleted ij> drop table row1; 0 rows inserted/updated/deleted ij> create table row1(c1 int); 0 rows inserted/updated/deleted ij> alter table row1 locksize row; 0 rows inserted/updated/deleted ij> drop table table1; 0 rows inserted/updated/deleted ij> create table table1(c1 int); 0 rows inserted/updated/deleted ij> alter table table1 locksize table; 0 rows inserted/updated/deleted ij> set isolation serializable; 0 rows inserted/updated/deleted ij> -- all selects should be table locked since no where clause select * from default1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all selects should be table locked since no where clause select * from default1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for DEFAULT1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from row1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from row1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for ROW1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from table1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from table1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for TABLE1 at serializable isolation level using share table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> -- scans for all updates should be table locked -- (No indexes, so will always do table scan) update default1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- scans for all updates should be table locked -- (No indexes, so will always do table scan) update default1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update default1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update default1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> update row1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update row1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update row1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update row1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> update table1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update table1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update table1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update table1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- all inserts should be row locked except for table1 insert into default1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- all inserts should be row locked except for table1 insert into default1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using row locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> insert into row1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into row1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using row locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> insert into table1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into table1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using table locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> -- scans for all deletes should be table locked -- (No indexes, so will always do table scan) delete from default1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- scans for all deletes should be table locked -- (No indexes, so will always do table scan) delete from default1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from default1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from default1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from row1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from row1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from row1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from row1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from table1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from table1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from table1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from table1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at serializable isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> -- alter table -- first set to same value (stupid test) alter table default1 locksize row; 0 rows inserted/updated/deleted ij> alter table row1 locksize row; 0 rows inserted/updated/deleted ij> alter table table1 locksize table; 0 rows inserted/updated/deleted ij> select tablename, lockGranularity from sys.systables where tablename in ('DEFAULT1', 'ROW1', 'TABLE1') order by tablename; TABLENAME |& ---------------------------------------------------------------------------------------------------------------------------------- DEFAULT1 |R ROW1 |R TABLE1 |T ij> -- set to opposite value alter table default1 locksize table; 0 rows inserted/updated/deleted ij> alter table row1 locksize table; 0 rows inserted/updated/deleted ij> alter table table1 locksize row; 0 rows inserted/updated/deleted ij> select tablename, lockGranularity from sys.systables where tablename in ('DEFAULT1', 'ROW1', 'TABLE1') order by tablename; TABLENAME |& ---------------------------------------------------------------------------------------------------------------------------------- DEFAULT1 |T ROW1 |T TABLE1 |R ij> set isolation read committed; 0 rows inserted/updated/deleted ij> -- verify lock granularity changed for selects select * from default1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- verify lock granularity changed for selects select * from default1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for DEFAULT1 at read committed isolation level using instantaneous share table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from row1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from row1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for ROW1 at read committed isolation level using instantaneous share table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> select * from table1; C1 ----------- ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: select * from table1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Table Scan ResultSet for TABLE1 at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> -- verify lock granularity changed for updates update default1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- verify lock granularity changed for updates update default1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update default1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update default1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> update row1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update row1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update row1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update row1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using table locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> update table1 set c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update table1 set c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: None ij> update table1 set c1 = 1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: update table1 set c1 = 1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Update ResultSet using row locking: deferred: false Rows updated = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> -- verify lock granularity changed for inserts insert into default1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- verify lock granularity changed for inserts insert into default1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using table locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> insert into row1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into row1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using table locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> insert into table1 values 1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: insert into table1 values 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Insert ResultSet using row locking: deferred: false insert mode: normal Rows inserted = 1 Indexes updated = 0 Execute Time = 0 Row ResultSet: Number of opens = 1 Rows returned = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 ij> -- verify lock granularity changed for deletes delete from default1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: -- verify lock granularity changed for deletes delete from default1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from default1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from default1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for DEFAULT1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from row1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from row1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from row1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from row1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using table locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for ROW1 at read committed isolation level using exclusive table locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from table1; 1 row inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from table1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 1 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 1 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 1 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched={} Number of columns fetched=0 Number of pages visited=1 Number of rows qualified=1 Number of rows visited=1 Scan type=heap start position: null stop position: null qualifiers: None ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> delete from table1 where c1 = 1; 0 rows inserted/updated/deleted ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); 1 ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Statement Name: null Statement Text: delete from table1 where c1 = 1 Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Delete ResultSet using row locking: deferred: false Rows deleted = 0 Indexes updated = 0 Execute Time = 0 Project-Restrict ResultSet (1): Number of opens = 1 Rows seen = 0 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TABLE1 at read committed isolation level using exclusive row locking chosen by the optimizer Number of opens = 1 Rows seen = 0 Rows filtered = 0 Fetch Size = 1 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 scan information: Bit set of columns fetched=All Number of columns fetched=1 Number of pages visited=1 Number of rows qualified=0 Number of rows visited=0 Scan type=heap start position: null stop position: null qualifiers: Column[0][0] Id: 0 Operator: = Ordered nulls: false Unknown return value: false Negate comparison result: false ij> CALL WAIT_FOR_POST_COMMIT(); 0 rows inserted/updated/deleted ij> -- bug 3819; delete from table would first -- end up getting an IX lock on table then -- an X lock on table; this can lead to -- deadlocks with multiple threads doing -- delete from table. fix is to choose -- row locking for deletes/updates in *all* -- cases; this would result in an IX lock -- on the table. means more locking but -- increased concurrency. insert into default1 values (1); 1 row inserted/updated/deleted ij> insert into default1 values (2); 1 row inserted/updated/deleted ij> select * from default1 order by c1; C1 ----------- 1 2 ij> set isolation to CURSOR STABILITY; 0 rows inserted/updated/deleted ij> autocommit off; ij> delete from default1; 2 rows inserted/updated/deleted ij> -- should see only one lock; earlier used to -- see 2, one IX and one for X. select count(*) from syscs_diag.lock_table where tablename = 'DEFAULT1' and type = 'TABLE'; 1 ----------- 1 ij> commit; ij> -- cleanup drop procedure WAIT_FOR_POST_COMMIT; 0 rows inserted/updated/deleted ij> drop table default1; 0 rows inserted/updated/deleted ij> drop table row1; 0 rows inserted/updated/deleted ij> drop table table1; 0 rows inserted/updated/deleted ij>