heapscan.out

derby database source code.good for you.

ij> --------------------------------------------------------------------------------
-- Test multi user lock interaction of ddl. 
--------------------------------------------------------------------------------
run resource 'createTestProcedures.subsql';
ij> CREATE FUNCTION  PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
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> autocommit off;
ij> connect 'wombat' as deleter;
ij(DELETER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(DELETER)> connect 'wombat' as scanner;
ij(SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij(SCANNER)> -- set up
set connection scanner;
ij(SCANNER)> set isolation CS;
0 rows inserted/updated/deleted
ij(SCANNER)> run resource 'LockTableQuery.subsql';
ij(SCANNER)> create view lock_table as
select 
    cast(username as char(8)) as username,
    cast(t.type as char(8)) as trantype,
    cast(l.type as char(8)) as type,
    cast(lockcount as char(3)) as cnt,
    mode,
    cast(tablename as char(12)) as tabname,
    cast(lockname as char(10)) as lockname,
    state,
    status
from 
    new org.apache.derby.diag.LockTable() l  right outer join new org.apache.derby.diag.TransactionTable() t
on l.xid = t.xid where l.tableType <> 'S' and t.type='UserTransaction';
0 rows inserted/updated/deleted
ij(SCANNER)> --on l.xid = t.xid where l.tableType <> 'S' or l.tableType is null
-- order by
--     tabname, type desc, mode, cnt, lockname
-- lock table with system catalog locks included.
create view full_lock_table as
select 
    cast(username as char(8)) as username,
    cast(t.type as char(8)) as trantype,
    cast(l.type as char(8)) as type,
    cast(lockcount as char(3)) as cnt,
    mode,
    cast(tablename as char(12)) as tabname,
    cast(lockname as char(10)) as lockname,
    state,
    status
from 
    new org.apache.derby.diag.LockTable() l right outer join new org.apache.derby.diag.TransactionTable() t
on l.xid = t.xid where l.tableType <> 'S' ;
0 rows inserted/updated/deleted
ij(SCANNER)> -- lock table with no join.
create view lock_table2 as
select 
    cast(l.xid as char(8)) as xid,
    cast(l.type as char(8)) as type,
    cast(lockcount as char(3)) as cnt,
    mode,
    cast(tablename as char(12)) as tabname,
    cast(lockname as char(10)) as lockname,
    state
from 
    new org.apache.derby.diag.LockTable() l  
where l.tableType <> 'S' ;
0 rows inserted/updated/deleted
ij(SCANNER)> -- transaction table with no join.
create view tran_table as
select 
    *
from 
    new org.apache.derby.diag.TransactionTable() t ;
0 rows inserted/updated/deleted
ij(SCANNER)> autocommit off;
ij(SCANNER)> drop table data;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'DATA' because it does not exist.
ij(SCANNER)> -- create a table with 2 rows per page.
create table data (keycol int, data varchar(2000)) ;
0 rows inserted/updated/deleted
ij(SCANNER)> insert into data values (0, PADSTRING('0',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (10, PADSTRING('100',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (20, PADSTRING('200',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (30, PADSTRING('300',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (40, PADSTRING('400',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (50, PADSTRING('100',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (60, PADSTRING('200',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (70, PADSTRING('300',2000));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (80, PADSTRING('400',2000));
1 row inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> set connection deleter;
ij(DELETER)> set current isolation = cursor stability;
0 rows inserted/updated/deleted
ij(DELETER)> autocommit off;
ij(DELETER)> commit;
ij(DELETER)> --------------------------------------------------------------------------------
-- Test 0: position scanner in the middle of the dataset using group commit
--         in a read commited scan which uses zero duration locks, then have
--         deleter remove all the rows in the table except for the last one, 
--         and wait long enough for the post commit job to reclaim the page 
--         that the scanner is positioned on.  Then do a next on the scanner
--         and verify the scanner goes to the last page.
--------------------------------------------------------------------------------
set connection scanner;
ij(SCANNER)> create table just_to_block_on (a int);
0 rows inserted/updated/deleted
ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2');
0 rows inserted/updated/deleted
ij(SCANNER)> get cursor scan_cursor as
    'select keycol from data';
ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
0 rows inserted/updated/deleted
ij(SCANNER)> next scan_cursor;
KEYCOL     
-----------
0          
ij(SCANNER)> 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   |IS  |DATA        |Tablelock |GRANT|ACTIVE  
APP     |UserTran|TABLE   |1   |X   |JUST_TO_BLOC|Tablelock |GRANT|ACTIVE  
ij(SCANNER)> -- now delete all rows but the last one, space should be reclaimed before
-- the scanner gets a chance to run.
set connection deleter;
ij(DELETER)> select 
    conglomeratename, isindex, 
    numallocatedpages, numfreepages, pagesize, estimspacesaving
from 
    new org.apache.derby.diag.SpaceTable('DATA') t
        order by conglomeratename;
CONGLOMERATENAME                                                                                                                |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE   |ESTIMSPACESAVING    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DATA                                                                                                                            |0     |9                   |0                   |4096       |0                   
ij(DELETER)> commit;
ij(DELETER)> delete from data where keycol < 80;
8 rows inserted/updated/deleted
ij(DELETER)> 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   |IS  |DATA        |Tablelock |GRANT|ACTIVE  
APP     |UserTran|TABLE   |2   |IX  |DATA        |Tablelock |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |X   |DATA        |(1,7)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |X   |DATA        |(2,6)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |X   |DATA        |(3,6)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |X   |DATA        |(4,6)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |X   |DATA        |(5,6)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |X   |DATA        |(6,6)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |X   |DATA        |(7,6)     |GRANT|ACTIVE  
APP     |UserTran|ROW     |1   |X   |DATA        |(8,6)     |GRANT|ACTIVE  
APP     |UserTran|TABLE   |1   |X   |JUST_TO_BLOC|Tablelock |GRANT|ACTIVE  
ij(DELETER)> commit;
ij(DELETER)> -- give post commit a chance to run, by hanging on a lock.
drop table just_to_block_on;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(DELETER)> commit;
ij(DELETER)> select 
    conglomeratename, isindex, 
    numallocatedpages, numfreepages, pagesize, estimspacesaving
from 
    new org.apache.derby.diag.SpaceTable('DATA') t
        order by conglomeratename;
CONGLOMERATENAME                                                                                                                |ISIND&|NUMALLOCATEDPAGES   |NUMFREEPAGES        |PAGESIZE   |ESTIMSPACESAVING    
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DATA                                                                                                                            |0     |2                   |7                   |4096       |28672               
ij(DELETER)> commit;
ij(DELETER)> set connection scanner;
ij(SCANNER)> -- this will return 10, from the group buffer (this looks wierd as 10 is 
-- deleted at this point - but that is what you get with read committed).
next scan_cursor;
KEYCOL     
-----------
10         
ij(SCANNER)> -- this will now go through the code which handles jumping over deleted pages.
next scan_cursor;
KEYCOL     
-----------
80         
ij(SCANNER)> commit;
ij(SCANNER)> --------------------------------------------------------------------------------
-- cleanup
--------------------------------------------------------------------------------
set connection deleter;
ij(DELETER)> commit;
ij(DELETER)> disconnect;
ij> set connection scanner;
ij(SCANNER)> drop table data;
0 rows inserted/updated/deleted
ij(SCANNER)> drop table just_to_block_on;
0 rows inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> disconnect;
ij> --------------------------------------------------------------------------------
-- Test 1: position scanner in the middle of the dataset using group commit
--         in a read commited scan which uses zero duration locks.  Now arrange
--         for the row the scan is positioned on to be purged by post commit,
--         but leave a row on the page for scan to reposition to.
--------------------------------------------------------------------------------
---------------
-- setup
---------------
autocommit off;
IJ ERROR: Unable to establish connection
ij> connect 'wombat' as deleter1;
ij(DELETER1)> connect 'wombat' as deleter2;
ij(DELETER2)> connect 'wombat' as scanner;
ij(SCANNER)> connect 'wombat' as lockholder;
ij(LOCKHOLDER)> -- set up
set connection scanner;
ij(SCANNER)> set isolation to read committed;
0 rows inserted/updated/deleted
ij(SCANNER)> autocommit off;
ij(SCANNER)> drop table data;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'DATA' because it does not exist.
ij(SCANNER)> -- create a table with 4 rows per page.
create table data (keycol int, data varchar(900));
0 rows inserted/updated/deleted
ij(SCANNER)> insert into data values (0, PADSTRING('0',900));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (10, PADSTRING('100',900));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (20, PADSTRING('200',900));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (30, PADSTRING('300',900));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (40, PADSTRING('400',900));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (50, PADSTRING('100',900));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (60, PADSTRING('200',900));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (70, PADSTRING('300',900));
1 row inserted/updated/deleted
ij(SCANNER)> insert into data values (80, PADSTRING('400',900));
1 row inserted/updated/deleted
ij(SCANNER)> create unique index idx on data (keycol);
0 rows inserted/updated/deleted
ij(SCANNER)> commit;
ij(SCANNER)> set connection deleter1;
ij(DELETER1)> set isolation read committed;
0 rows inserted/updated/deleted
ij(DELETER1)> autocommit off;
ij(DELETER1)> commit;
ij(DELETER1)> set connection deleter2;
ij(DELETER2)> set isolation READ COMMITTED;
0 rows inserted/updated/deleted
ij(DELETER2)> autocommit off;
ij(DELETER2)> commit;
ij(DELETER2)> set connection lockholder;
ij(LOCKHOLDER)> set CURRENT isolation TO CS;
0 rows inserted/updated/deleted
ij(LOCKHOLDER)> autocommit off;
ij(LOCKHOLDER)> commit;
ij(LOCKHOLDER)> --------------
-- run the test
--------------
set connection lockholder;
ij(LOCKHOLDER)> create table just_to_block_on (a int);
0 rows inserted/updated/deleted
ij(LOCKHOLDER)> set connection scanner;
ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2');