ddltablelockmode.out

derby database source code.good for you.

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