heapscan.out

derby database source code.good for you.

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 isolation read committed;
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');
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)> next scan_cursor;
KEYCOL     
-----------
10         
ij(SCANNER)> next scan_cursor;
KEYCOL     
-----------
20         
ij(SCANNER)> next scan_cursor;
KEYCOL     
-----------
30         
ij(SCANNER)> next scan_cursor;
KEYCOL     
-----------
40         
ij(SCANNER)> next scan_cursor;
KEYCOL     
-----------
50         
ij(SCANNER)> -- scan is now positioned on row (50, 500), as it group fetched in 2 row chunks.
-- In the deleter1 thread delete the 1st row on the page, but don't commit:
-- (40, 400).
-- In the deleter2 thread delete the current row and the rows following on the
-- page, and commit: (50, 500), (60, 600), (70, 700).  This will result in
-- the code seeing a page with all rows deleted and then queue a post commit on
-- the page which will purge 50, 60, and 70, but it won't be able to reclaim 
-- the one that has not been committed by deleter1.
-- delete in this transaction keycol (30, 300).
set connection deleter1;
ij(DELETER1)> delete from data where keycol = 40;
1 row inserted/updated/deleted
ij(DELETER1)> -- delete in this transaction the rest of rows on the page.
set connection deleter2;
ij(DELETER2)> delete from data where keycol = 50;
1 row inserted/updated/deleted
ij(DELETER2)> delete from data where keycol = 60;
1 row inserted/updated/deleted
ij(DELETER2)> delete from data where keycol = 70;
1 row inserted/updated/deleted
ij(DELETER2)> commit;
ij(DELETER2)> -- block deleter threads on a lock to give post commit a chance to run.
set connection deleter2;
ij(DELETER2)> select * from just_to_block_on;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(DELETER2)> -- now assume post commit has run, commit deleter1 so that one deleted
-- row remains on the page after the positioned row.
set connection deleter1;
ij(DELETER1)> commit;
ij(DELETER1)> -- the scanner gets a chance to run.
set connection scanner;
ij(SCANNER)> -- now at this point the scanner will resume and find the row it is positioned
-- on has been purged, the only rows following it to be deleted and it will 
-- reposition automatically to (80, 800) on the next page.
next scan_cursor;
KEYCOL     
-----------
80         
ij(SCANNER)> next scan_cursor;
No current row
ij(SCANNER)> commit;
ij(SCANNER)> select * from data;
KEYCOL     |DATA                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------
0          |0                                                                                                                              &
10         |100                                                                                                                            &
20         |200                                                                                                                            &
30         |300                                                                                                                            &
80         |400                                                                                                                            &
ij(SCANNER)> commit;
ij(SCANNER)> --------------------------------------------------------------------------------
-- cleanup
--------------------------------------------------------------------------------
set connection scanner;
ij(SCANNER)> disconnect;
ij> set connection deleter1;
ij(DELETER1)> disconnect;
ij> set connection deleter2;
ij(DELETER2)> disconnect;
ij> set connection deleter2;
IJ ERROR: No connection exists with the name DELETER2
ij> disconnect;
IJ ERROR: Unable to establish connection
ij> set connection lockholder;
ij(LOCKHOLDER)> disconnect;
ij> --------------------------------------------------------------------------------
-- Test 4: position scanner in the middle of the dataset using group commit
--         in a read commited scan which uses zero duration locks.  Now arrange
--         for all rows in the table to be purged.  The reposition code will
--         attempt to position on the "next" page, and find no more pages.
--------------------------------------------------------------------------------
---------------
-- setup
---------------
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 read committed;
0 rows inserted/updated/deleted
ij(SCANNER)> autocommit off;
ij(SCANNER)> drop table data;
0 rows inserted/updated/deleted
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 isolation read committed;
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');
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)> next scan_cursor;
KEYCOL     
-----------
10         
ij(SCANNER)> next scan_cursor;
KEYCOL     
-----------
20         
ij(SCANNER)> next scan_cursor;
KEYCOL     
-----------
30         
ij(SCANNER)> next scan_cursor;
KEYCOL     
-----------
40         
ij(SCANNER)> next scan_cursor;
KEYCOL     
-----------
50         
ij(SCANNER)> -- scan is now positioned on row (50, 500), as it group fetched in 2 row chunks.
-- In the deleter1 thread delete all the rows, allowing all rows/pages to be
-- reclaimed.
-- delete in this transaction all rows.
set connection deleter1;
ij(DELETER1)> delete from data where keycol >= 0 ;
9 rows inserted/updated/deleted
ij(DELETER1)> commit;
ij(DELETER1)> -- block deleter threads on a lock to give post commit a chance to run.
set connection deleter2;
ij(DELETER2)> select * from just_to_block_on;
ERROR 40XL1: A lock could not be obtained within the time requested
ij(DELETER2)> -- now assume post commit has run, commit deleter1 so that one deleted
-- row remains on the page after the positioned row.
commit;
ij(DELETER2)> -- the scanner gets a chance to run.
set connection scanner;
ij(SCANNER)> -- now at this point the scanner will resume and find the row it is positioned
-- on has been purged, and no rows or pages remaining in the table.
next scan_cursor;
No current row
ij(SCANNER)> next scan_cursor;
No current row
ij(SCANNER)> commit;
ij(SCANNER)> select * from data;
KEYCOL     |DATA                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------------------
ij(SCANNER)> commit;
ij(SCANNER)> --------------------------------------------------------------------------------
-- cleanup
--------------------------------------------------------------------------------
set connection scanner;
ij(SCANNER)> disconnect;
ij> set connection deleter1;
ij(DELETER1)> disconnect;
ij> set connection deleter2;
ij(DELETER2)> disconnect;
ij> set connection deleter2;
IJ ERROR: No connection exists with the name DELETER2
ij> disconnect;
IJ ERROR: Unable to establish connection
ij> set connection lockholder;
ij(LOCKHOLDER)> disconnect;
ij> exit;