heapscan.sql

derby database source code.good for you.

-- In the deleter1 thread delete the last row on the page, but don't commit.
-- in the other deleter thread delete the rest of the rows on the page and
-- commit it, which will result in a post commit to try and reclaim all the
-- rows on the page, 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;
delete from data where keycol = 30;

-- delete in this transaction the rest of rows on the page.
set connection deleter2;
delete from data where keycol = 0;
delete from data where keycol = 10;
delete from data where keycol = 20;
commit;

-- block deleter threads on a lock to give post commit a chance to run.
set connection deleter2;
select * from just_to_block_on;

-- now assume post commit has run, commit deleter1 so that one deleted
-- row remains on the page after the positioned row.
set connection deleter1;
commit;

-- the scanner gets a chance to run.
set connection 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 (40, 400) on the next page.
next scan_cursor;
next scan_cursor;
next scan_cursor;
commit;

select * from data;

commit;

--------------------------------------------------------------------------------
-- cleanup
--------------------------------------------------------------------------------
set connection scanner;
disconnect;
set connection deleter1;
disconnect;
set connection deleter2;
disconnect;
set connection deleter2;
disconnect;
set connection lockholder;
disconnect;

--------------------------------------------------------------------------------
-- Test 3: 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, and all rows following it on
--         the page to be purged by post commit, but leave at least one row on
--         the page so that the page is not removed.  The reposition code will
--         position on the page, find the row has disappeared, ask for the
--         "next" row on the page, find that no such row exists on the page,
--         and finally move to the next page.
--------------------------------------------------------------------------------

---------------
-- setup
---------------
connect 'wombat' as deleter1;

connect 'wombat' as deleter2;

connect 'wombat' as scanner;

connect 'wombat' as lockholder;

-- set up
set connection scanner;
set isolation read committed;
autocommit off;
drop table data;
-- create a table with 4 rows per page.
create table data (keycol int, data varchar(900)) ;
insert into data values (0, PADSTRING('0',900));
insert into data values (10, PADSTRING('100',900));
insert into data values (20, PADSTRING('200',900));
insert into data values (30, PADSTRING('300',900));
insert into data values (40, PADSTRING('400',900));
insert into data values (50, PADSTRING('100',900));
insert into data values (60, PADSTRING('200',900));
insert into data values (70, PADSTRING('300',900));
insert into data values (80, PADSTRING('400',900));
create unique index idx on data (keycol);
commit;

set connection deleter1;
set isolation read committed;
autocommit off;
commit;
set connection deleter2;
set isolation read committed;
autocommit off;
commit;
set connection lockholder;
set isolation read committed;
autocommit off;
commit;

--------------
-- run the test
--------------

set connection lockholder;
create table just_to_block_on (a int);

set connection scanner;

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2');
get cursor scan_cursor as
    'select keycol from data';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

next scan_cursor;
next scan_cursor;
next scan_cursor;
next scan_cursor;
next scan_cursor;
next scan_cursor;

-- 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;
delete from data where keycol = 40;

-- delete in this transaction the rest of rows on the page.
set connection deleter2;
delete from data where keycol = 50;
delete from data where keycol = 60;
delete from data where keycol = 70;
commit;

-- block deleter threads on a lock to give post commit a chance to run.
set connection deleter2;
select * from just_to_block_on;

-- now assume post commit has run, commit deleter1 so that one deleted
-- row remains on the page after the positioned row.
set connection deleter1;
commit;

-- the scanner gets a chance to run.
set connection 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;
next scan_cursor;
commit;

select * from data;

commit;

--------------------------------------------------------------------------------
-- cleanup
--------------------------------------------------------------------------------
set connection scanner;
disconnect;
set connection deleter1;
disconnect;
set connection deleter2;
disconnect;
set connection deleter2;
disconnect;
set connection lockholder;
disconnect;

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

connect 'wombat' as deleter2;

connect 'wombat' as scanner;

connect 'wombat' as lockholder;

-- set up
set connection scanner;
set isolation read committed;
autocommit off;
drop table data;
-- create a table with 4 rows per page.
create table data (keycol int, data varchar(900)) ;
insert into data values (0, PADSTRING('0',900));
insert into data values (10, PADSTRING('100',900));
insert into data values (20, PADSTRING('200',900));
insert into data values (30, PADSTRING('300',900));
insert into data values (40, PADSTRING('400',900));
insert into data values (50, PADSTRING('100',900));
insert into data values (60, PADSTRING('200',900));
insert into data values (70, PADSTRING('300',900));
insert into data values (80, PADSTRING('400',900));
create unique index idx on data (keycol);
commit;

set connection deleter1;
set isolation read committed;
autocommit off;
commit;
set connection deleter2;
set isolation read committed;
autocommit off;
commit;
set connection lockholder;
set isolation read committed;
autocommit off;
commit;

--------------
-- run the test
--------------

set connection lockholder;
create table just_to_block_on (a int);

set connection scanner;

CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2');
get cursor scan_cursor as
    'select keycol from data';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

next scan_cursor;
next scan_cursor;
next scan_cursor;
next scan_cursor;
next scan_cursor;
next scan_cursor;

-- 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;
delete from data where keycol >= 0 ;
commit;

-- block deleter threads on a lock to give post commit a chance to run.
set connection deleter2;
select * from just_to_block_on;

-- now assume post commit has run, commit deleter1 so that one deleted
-- row remains on the page after the positioned row.
commit;

-- the scanner gets a chance to run.
set connection 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;
next scan_cursor;
commit;

select * from data;

commit;

--------------------------------------------------------------------------------
-- cleanup
--------------------------------------------------------------------------------
set connection scanner;
disconnect;
set connection deleter1;
disconnect;
set connection deleter2;
disconnect;
set connection deleter2;
disconnect;
set connection lockholder;
disconnect;

exit;