isolationlevels.out

derby database source code.good for you.

ij> -- single user test for the various isolation levels
prepare getIsolation as 'values current isolation';
ij> autocommit off;
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 2000;
ij> -- create a table
create table t1(c1 int not null constraint asdf primary key);
0 rows inserted/updated/deleted
ij> commit;
ij> -- insert a row
insert into t1 values 1;
1 row inserted/updated/deleted
ij> -- verify table scan gets row lock at read committed
select * from t1;
C1         
-----------
1          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- verify table scan gets row lock at read committed
select * from t1
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: 
Index Scan ResultSet for T1 using constraint ASDF at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 16
	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={0}
	Number of columns fetched=1
	Number of deleted rows visited=0
	Number of pages visited=1
	Number of rows qualified=1
	Number of rows visited=1
	Scan type=btree
	Tree height=1
	start position: 
	None
	stop position: 
	None
	qualifiers:
None
ij> -- verify matching index scan gets row lock at read committed
select * from t1 where c1 = 1;
C1         
-----------
1          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- verify matching index scan gets row lock at read committed
select * from t1 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: 
Index Scan ResultSet for T1 using constraint ASDF at read committed isolation level using share 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={0}
	Number of columns fetched=1
	Number of deleted rows visited=0
	Number of pages visited=1
	Number of rows qualified=1
	Number of rows visited=1
	Scan type=btree
	Tree height=1
	start position: 
	>= on first 1 column(s).
	Ordered null semantics on the following columns: 
0 
	stop position: 
	> on first 1 column(s).
	Ordered null semantics on the following columns: 
0 
	qualifiers:
None
ij> -- verify WITH clause works
select * from t1 with rr;
C1         
-----------
1          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- verify WITH clause works
select * from t1 with rr
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: 
Index Scan ResultSet for T1 using constraint ASDF at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 16
	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={0}
	Number of columns fetched=1
	Number of deleted rows visited=0
	Number of pages visited=1
	Number of rows qualified=1
	Number of rows visited=1
	Scan type=btree
	Tree height=1
	start position: 
	None
	stop position: 
	None
	qualifiers:
None
ij> -- verify SET ISOLATION commits and changes isolation level
set isolation RR;
0 rows inserted/updated/deleted
ij> execute getIsolation;
1   
----
RR  
ij> -- rollback should find nothing to undo
rollback;
ij> select * from t1;
C1         
-----------
1          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select * from t1
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: 
Index Scan ResultSet for T1 using constraint ASDF at serializable isolation level using share table locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 16
	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={0}
	Number of columns fetched=1
	Number of deleted rows visited=0
	Number of pages visited=1
	Number of rows qualified=1
	Number of rows visited=1
	Scan type=btree
	Tree height=1
	start position: 
	None
	stop position: 
	None
	qualifiers:
None
ij> set isolation reset;
0 rows inserted/updated/deleted
ij> execute getIsolation;
1   
----
ij> -- verify matching index scan gets row lock at read committed
select * from t1 where c1 = 1;
C1