java/testing/org/apache/derbyTesting/functionTests/tests/store/heapscan.sql - derby - Git at Google

 --
 --   Licensed to the Apache Software Foundation (ASF) under one or more
 --   contributor license agreements.  See the NOTICE file distributed with
 --   this work for additional information regarding copyright ownership.
 --   The ASF licenses this file to You under the Apache License, Version 2.0
 --   (the "License"); you may not use this file except in compliance with
 --   the License.  You may obtain a copy of the License at
 --
 --      http://www.apache.org/licenses/LICENSE-2.0
 --
 --   Unless required by applicable law or agreed to in writing, software
 --   distributed under the License is distributed on an "AS IS" BASIS,
 --   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 --   See the License for the specific language governing permissions and
 --   limitations under the License.
 --
 --------------------------------------------------------------------------------
 -- Test multi user lock interaction of ddl.
 --------------------------------------------------------------------------------
 run resource '/org/apache/derbyTesting/functionTests/tests/store/createTestProcedures.subsql';
 autocommit off;

 connect 'wombat' as deleter;
 -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
 NoHoldForConnection;

 connect 'wombat' as scanner;
 -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
 NoHoldForConnection;

 -- set up
 set connection scanner;
 set isolation CS;
 run resource '/org/apache/derbyTesting/functionTests/tests/store/LockTableQuery.subsql';
 autocommit off;
 drop table data;
 -- create a table with 2 rows per page.
 create table data (keycol int, data varchar(2000)) ;
 insert into data values (0, PADSTRING('0',2000));
 insert into data values (10, PADSTRING('100',2000));
 insert into data values (20, PADSTRING('200',2000));
 insert into data values (30, PADSTRING('300',2000));
 insert into data values (40, PADSTRING('400',2000));
 insert into data values (50, PADSTRING('100',2000));
 insert into data values (60, PADSTRING('200',2000));
 insert into data values (70, PADSTRING('300',2000));
 insert into data values (80, PADSTRING('400',2000));
 commit;

 set connection deleter;
 set current isolation = cursor stability;
 autocommit off;
 commit;

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

 create table just_to_block_on (a int);
 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;

 select * from lock_table order by tabname, type desc, mode, cnt, lockname;

 -- now delete all rows but the last one, space should be reclaimed before
 -- the scanner gets a chance to run.
 set connection deleter;

 select
     conglomeratename, isindex,
     numallocatedpages, numfreepages, pagesize, estimspacesaving
 from
     new org.apache.derby.diag.SpaceTable('DATA') t
         order by conglomeratename;
 commit;

 delete from data where keycol < 80;

 select * from lock_table order by tabname, type desc, mode, cnt, lockname;

 commit;

 -- give post commit a chance to run, by hanging on a lock.
 drop table just_to_block_on;

 commit;

 select
     conglomeratename, isindex,
     numallocatedpages, numfreepages, pagesize, estimspacesaving
 from
     new org.apache.derby.diag.SpaceTable('DATA') t
         order by conglomeratename;
 commit;

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

 -- this will now go through the code which handles jumping over deleted pages.
 next scan_cursor;

 commit;

 --------------------------------------------------------------------------------
 -- cleanup
 --------------------------------------------------------------------------------
 set connection deleter;
 commit;
 disconnect;
 set connection scanner;
 drop table data;
 drop table just_to_block_on;
 commit;
 disconnect;

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

 connect 'wombat' as deleter1;

 connect 'wombat' as deleter2;

 connect 'wombat' as scanner;

 connect 'wombat' as lockholder;

 -- set up
 set connection scanner;
 set isolation to 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 CURRENT isolation TO CS;
 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;

 -- scan is now positioned on row (10, 100), as it group fetched 2 rows.

 -- in 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;
 set connection deleter1;
 select * from just_to_block_on;

 -- now assume post commit has run, roll back deleter1 so that one non-deleted
 -- row remains on the page.
 set connection deleter1;
 rollback;

 -- 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 it will reposition automatically to (30, 300) on
 -- the same 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 lockholder;
 disconnect;

 --------------------------------------------------------------------------------
 -- Test 2: 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, as did Test 1.
 --         This time make the row left on the page be deleted, so when the
 --         scan repositions, it should jump over the deleted row.
 --------------------------------------------------------------------------------

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

 -- scan is now positioned on row (10, 100), as it group fetched 2 rows.

 -- 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;
	--
	-- Licensed to the Apache Software Foundation (ASF) under one or more
	-- contributor license agreements. See the NOTICE file distributed with
	-- this work for additional information regarding copyright ownership.
	-- The ASF licenses this file to You under the Apache License, Version 2.0
	-- (the "License"); you may not use this file except in compliance with
	-- the License. You may obtain a copy of the License at
	--
	-- http://www.apache.org/licenses/LICENSE-2.0
	--
	-- Unless required by applicable law or agreed to in writing, software
	-- distributed under the License is distributed on an "AS IS" BASIS,
	-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
	-- See the License for the specific language governing permissions and
	-- limitations under the License.
	--
	--------------------------------------------------------------------------------
	-- Test multi user lock interaction of ddl.
	--------------------------------------------------------------------------------
	run resource '/org/apache/derbyTesting/functionTests/tests/store/createTestProcedures.subsql';
	autocommit off;

	connect 'wombat' as deleter;
	-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
	NoHoldForConnection;

	connect 'wombat' as scanner;
	-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
	NoHoldForConnection;

	-- set up
	set connection scanner;
	set isolation CS;
	run resource '/org/apache/derbyTesting/functionTests/tests/store/LockTableQuery.subsql';
	autocommit off;
	drop table data;
	-- create a table with 2 rows per page.
	create table data (keycol int, data varchar(2000)) ;
	insert into data values (0, PADSTRING('0',2000));
	insert into data values (10, PADSTRING('100',2000));
	insert into data values (20, PADSTRING('200',2000));
	insert into data values (30, PADSTRING('300',2000));
	insert into data values (40, PADSTRING('400',2000));
	insert into data values (50, PADSTRING('100',2000));
	insert into data values (60, PADSTRING('200',2000));
	insert into data values (70, PADSTRING('300',2000));
	insert into data values (80, PADSTRING('400',2000));
	commit;

	set connection deleter;
	set current isolation = cursor stability;
	autocommit off;
	commit;

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

	create table just_to_block_on (a int);
	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;

	select * from lock_table order by tabname, type desc, mode, cnt, lockname;

	-- now delete all rows but the last one, space should be reclaimed before
	-- the scanner gets a chance to run.
	set connection deleter;

	select
	conglomeratename, isindex,
	numallocatedpages, numfreepages, pagesize, estimspacesaving
	from
	new org.apache.derby.diag.SpaceTable('DATA') t
	order by conglomeratename;
	commit;

	delete from data where keycol < 80;

	select * from lock_table order by tabname, type desc, mode, cnt, lockname;

	commit;

	-- give post commit a chance to run, by hanging on a lock.
	drop table just_to_block_on;

	commit;

	select
	conglomeratename, isindex,
	numallocatedpages, numfreepages, pagesize, estimspacesaving
	from
	new org.apache.derby.diag.SpaceTable('DATA') t
	order by conglomeratename;
	commit;

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

	-- this will now go through the code which handles jumping over deleted pages.
	next scan_cursor;

	commit;

	--------------------------------------------------------------------------------
	-- cleanup
	--------------------------------------------------------------------------------
	set connection deleter;
	commit;
	disconnect;
	set connection scanner;
	drop table data;
	drop table just_to_block_on;
	commit;
	disconnect;

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

	connect 'wombat' as deleter1;

	connect 'wombat' as deleter2;

	connect 'wombat' as scanner;

	connect 'wombat' as lockholder;

	-- set up
	set connection scanner;
	set isolation to 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 CURRENT isolation TO CS;
	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;

	-- scan is now positioned on row (10, 100), as it group fetched 2 rows.

	-- in 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;
	set connection deleter1;
	select * from just_to_block_on;

	-- now assume post commit has run, roll back deleter1 so that one non-deleted
	-- row remains on the page.
	set connection deleter1;
	rollback;

	-- 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 it will reposition automatically to (30, 300) on
	-- the same 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 lockholder;
	disconnect;

	--------------------------------------------------------------------------------
	-- Test 2: 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, as did Test 1.
	-- This time make the row left on the page be deleted, so when the
	-- scan repositions, it should jump over the deleted row.
	--------------------------------------------------------------------------------

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

	-- scan is now positioned on row (10, 100), as it group fetched 2 rows.

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