tablelockbasic.out

derby database source code.good for you.

ij> commit;
ij> --------------------------------------------------------------------------------
-- Test delete from a indexed heap, should get exclusive table lock.
--------------------------------------------------------------------------------
delete from indexed_heap where a = 1;
1 row inserted/updated/deleted
ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |2   |X   |INDEXED_HEAP|Tablelock |GRANT|ACTIVE  
ij> commit;
ij> --------------------------------------------------------------------------------
-- Test update to indexed heap, should get exclusive table lock.
--------------------------------------------------------------------------------
update indexed_heap set a = 1000 where a = 2;
1 row inserted/updated/deleted
ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |4   |X   |INDEXED_HEAP|Tablelock |GRANT|ACTIVE  
ij> commit;
ij> --------------------------------------------------------------------------------
-- Test drop of indexed heap, should get exclusive table lock.
--------------------------------------------------------------------------------
drop table indexed_heap;
0 rows inserted/updated/deleted
ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |X   |*** TRANSIEN|Tablelock |GRANT|ACTIVE  
APP     |UserTran|TABLE   |4   |X   |*** TRANSIEN|Tablelock |GRANT|ACTIVE  
ij> commit;
ij> --------------------------------------------------------------------------------
-- Test LOCK TABLE statement
--------------------------------------------------------------------------------
create table t1(c1 int);
0 rows inserted/updated/deleted
ij> commit;
ij> prepare p1 as 'lock table t1 in exclusive mode';
ij> execute p1;
0 rows inserted/updated/deleted
ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |X   |T1          |Tablelock |GRANT|ACTIVE  
ij> -- verify that statement gets recompiled correctly
drop table t1;
0 rows inserted/updated/deleted
ij> create table t1(c1 int);
0 rows inserted/updated/deleted
ij> execute p1;
0 rows inserted/updated/deleted
ij> commit;
ij> lock table t1 in share mode;
0 rows inserted/updated/deleted
ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
APP     |UserTran|TABLE   |1   |S   |T1          |Tablelock |GRANT|ACTIVE  
ij> drop table t1;
0 rows inserted/updated/deleted
ij> commit;
ij> -- verify that lock table not allowed in sys schema
lock table sys.systables in exclusive mode;
ERROR 42X62: 'LOCK TABLE' is not allowed in the 'SYS' schema.
ij> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
---------------------------------------------------------------------------
ij> commit;
ij> --------------------------------------------------------------------------------
-- Test RTS output when table locking configured
--------------------------------------------------------------------------------
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 2000;
ij> create table rts(c1 int);
0 rows inserted/updated/deleted
ij> insert into rts values 1;
1 row inserted/updated/deleted
ij> commit;
ij> select * from rts with cs;
C1         
-----------
1          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select * from rts with cs
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 RTS at read committed isolation level using instantaneous share row locking chosen by the optimizer (Actual locking used: table level locking.)
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=All
	Number of columns fetched=1
	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> drop table rts;
0 rows inserted/updated/deleted
ij> commit;
ij> --------------------------------------------------------------------------------
-- Test DDL TABLE LOCK MODE
--------------------------------------------------------------------------------
create table default_granularity(c1 int);
0 rows inserted/updated/deleted
ij> create table row_granularity(c1 int);
0 rows inserted/updated/deleted
ij> alter table row_granularity locksize row;
0 rows inserted/updated/deleted
ij> create table table_granularity(c1 int);
0 rows inserted/updated/deleted
ij> alter table table_granularity locksize table;
0 rows inserted/updated/deleted
ij> select * from default_granularity with cs;
C1         
-----------
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select * from default_granularity with cs
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 DEFAULT_GRANULARITY at read committed isolation level using instantaneous share row locking chosen by the optimizer (Actual locking used: table level locking.)
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 default_granularity with rr;
C1         
-----------
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	select * from default_granularity with rr
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0