java/testing/org/apache/derbyTesting/functionTests/tests/store/readBtreeSetLocks.subsql - 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.
 --
 -- Basic single user testing of read locks on select "set" queries on indexes.
 --
 -- This ".subsql" test is
 -- meant to be run from another test such that it gets run under multiple
 -- isolation levels.  This is important as they behave
 -- differently, depending on isolation levels.
 --
 -- assume's caller has already done: run 'LockTableQuery.subsql'; to get
 -- easy access to the lock VTI.

 -- TEST 0: btree scan
 -- TEST 1: btree scan, (scan table with some deleted rows)
 -- TEST 2: btree scan, (scan with "<" qualifier)
 -- TEST 3: btree scan, (scan with equals qualifier)
 -- TEST 4: btree scan, (equals qualifier, no rows return)

 autocommit off;

 --------------------------------------------------------------------------------
 -- Assumes that calling routine has set up the following simple dataset,
 -- a heap, and index with following initial values:
 --     create table a (a int, b int, c somesortofchar, [index_pad]);
 --     create index a_idx on a (a) or a_idx on a (a, index_pad);
 --
 -- 1, 10, 'one'
 -- 2, 20, 'two'
 -- 3, 30, 'three'
 -- 4, 40, 'four'
 -- 5, 50, 'five'
 -- 6, 60, 'six'
 -- 7, 70, 'seven'
 --------------------------------------------------------------------------------
 select * from a;
 commit;

 --------------------------------------------------------------------------------
 -- TEST 0: btree scan
 -- Test full scan.
 --    ALL MODES        - TABLE IS, "scan page lockname of form (pageno, 1)
 --    SERIALIZABLE     - row S locks as it visits each row, row/table lock
 --                       held until end xact.  Also holds one previous key lock.
 --    REPEATABLE READ  - row S locks as it visits each row, row/table locks held
 --                       until end of transaction.
 --    READ COMMITTED   - instantaneous S locks requested on each row.  No locks
 --                       held after query finishes.
 --    READ UNCOMMITTED - no row locks.  No locks held after query finishes.
 --------------------------------------------------------------------------------
 select a from a;
 select * from lock_table order by tabname, type desc, mode, cnt, lockname;
 commit;

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


 --------------------------------------------------------------------------------
 -- TEST 1: btree scan, (scan table with some deleted rows)
 -- Test full scan on a data set with some deleted rows (the "even" ones).
 --    ALL MODES        - TABLE IS, "scan page lockname of form (pageno, 1)
 --    SERIALIZABLE     - row S locks as it visits each row, row/table lock
 --                       held until end xact.  Also holds one previous key lock.
 --    REPEATABLE READ  - row S locks as it visits each row, row/table locks held
 --                       until end of transaction.
 --    READ COMMITTED   - instantaneous S locks requested on each row.  No locks
 --                       held after query finishes.
 --    READ UNCOMMITTED - no row locks.  No locks held after query finishes.
 --
 -- After the delete the base table should look like:
 -- 1, 10, 'one'
 -- 3, 30, 'three'
 -- 5, 50, 'five'
 -- 7, 70, 'seven'
 --------------------------------------------------------------------------------
 delete from a where a = 2 or a = 4 or a = 6;
 commit;

 -- The above delete and commit can allow post commit processing to run before
 -- the subsequent select is run, most likely if this run is on a table where
 -- there is one row per page.  To get reproducible results from the subsequent
 -- lock calls, wait for post commit to finish before running the select.
 -- Without this wait "extra" locks sometimes might show up - these are locks
 -- on the committed deleted rows which have not been cleaned yet depending
 -- on timing.
 CALL WAIT_FOR_POST_COMMIT();

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

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


 --------------------------------------------------------------------------------
 -- TEST 2: btree scan, (scan with "<" qualifier)
 -- Test "less than" qualified scan on a data set.
 --    ALL MODES        - TABLE IS, "scan page lockname of form (pageno, 1)
 --    SERIALIZABLE     - row S locks as it visits each row, row/table lock
 --                       held until end xact.  Also holds one previous key lock.
 --    REPEATABLE READ  - row S locks as it visits each row, row/table locks held
 --                       until end of transaction.
 --    READ COMMITTED   - instantaneous S locks requested on each row.  No locks
 --                       held after query finishes.
 --    READ UNCOMMITTED - no row locks.  No locks held after query finishes.
 --
 -- At this point the base table should look like:
 -- 1, 10, 'one'
 -- 3, 30, 'three'
 -- 5, 50, 'five'
 -- 7, 70, 'seven'
 --------------------------------------------------------------------------------

 select a from a where a < 3;
 select * from lock_table order by tabname, type desc, mode, cnt, lockname;
 commit;

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

 --------------------------------------------------------------------------------
 -- TEST 3: btree scan, (scan with equals qualifier)
 -- Test "equals" qualified cursor scan on a data set.
 --    ALL MODES        - TABLE IS, "scan page lockname of form (pageno, 1)
 --    SERIALIZABLE     - row S locks as it visits each row, row/table lock
 --                       held until end xact.  Also holds one previous key lock.
 --    REPEATABLE READ  - row S locks as it visits each row, row/table locks held
 --                       until end of transaction.
 --    READ COMMITTED   - instantaneous S locks requested on each row.  No locks
 --                       held after query finishes.
 --    READ UNCOMMITTED - no row locks.  No locks held after query finishes.
 --
 -- At this point the base table should look like:
 -- 1, 10, 'one'
 -- 3, 30, 'three'
 -- 5, 50, 'five'
 -- 7, 70, 'seven'
 --------------------------------------------------------------------------------

 select a from a where a = 5;
 select * from lock_table order by tabname, type desc, mode, cnt, lockname;
 commit;

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

 --------------------------------------------------------------------------------
 -- TEST  4: btree scan, (equals qualifier, no rows return)
 -- Test "equals" qualified scan on a data set, no rows returned.
 --    ALL MODES        - TABLE IS, "scan page lockname of form (pageno, 1)
 --    SERIALIZABLE     - row S locks as it visits each row, row/table lock
 --                       held until end xact.  Also holds one previous key lock.
 --    REPEATABLE READ  - row S locks as it visits each row, row/table locks held
 --                       until end of transaction.
 --    READ COMMITTED   - instantaneous S locks requested on each row.  No locks
 --                       held after query finishes.
 --    READ UNCOMMITTED - no row locks.  No locks held after query finishes.
 --
 -- At this point the base table should look like:
 -- 1, 10, 'one'
 -- 3, 30, 'three'
 -- 5, 50, 'five'
 -- 7, 70, 'seven'
 --------------------------------------------------------------------------------

 -- no rows expected to qualify
 select a from a where a = 42;
 select * from lock_table order by tabname, type desc, mode, cnt, lockname;
 commit;

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

 drop table a;
	--
	-- 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.
	--
	-- Basic single user testing of read locks on select "set" queries on indexes.
	--
	-- This ".subsql" test is
	-- meant to be run from another test such that it gets run under multiple
	-- isolation levels. This is important as they behave
	-- differently, depending on isolation levels.
	--
	-- assume's caller has already done: run 'LockTableQuery.subsql'; to get
	-- easy access to the lock VTI.

	-- TEST 0: btree scan
	-- TEST 1: btree scan, (scan table with some deleted rows)
	-- TEST 2: btree scan, (scan with "<" qualifier)
	-- TEST 3: btree scan, (scan with equals qualifier)
	-- TEST 4: btree scan, (equals qualifier, no rows return)

	autocommit off;

	--------------------------------------------------------------------------------
	-- Assumes that calling routine has set up the following simple dataset,
	-- a heap, and index with following initial values:
	-- create table a (a int, b int, c somesortofchar, [index_pad]);
	-- create index a_idx on a (a) or a_idx on a (a, index_pad);
	--
	-- 1, 10, 'one'
	-- 2, 20, 'two'
	-- 3, 30, 'three'
	-- 4, 40, 'four'
	-- 5, 50, 'five'
	-- 6, 60, 'six'
	-- 7, 70, 'seven'
	--------------------------------------------------------------------------------
	select * from a;
	commit;

	--------------------------------------------------------------------------------
	-- TEST 0: btree scan
	-- Test full scan.
	-- ALL MODES - TABLE IS, "scan page lockname of form (pageno, 1)
	-- SERIALIZABLE - row S locks as it visits each row, row/table lock
	-- held until end xact. Also holds one previous key lock.
	-- REPEATABLE READ - row S locks as it visits each row, row/table locks held
	-- until end of transaction.
	-- READ COMMITTED - instantaneous S locks requested on each row. No locks
	-- held after query finishes.
	-- READ UNCOMMITTED - no row locks. No locks held after query finishes.
	--------------------------------------------------------------------------------
	select a from a;
	select * from lock_table order by tabname, type desc, mode, cnt, lockname;
	commit;

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


	--------------------------------------------------------------------------------
	-- TEST 1: btree scan, (scan table with some deleted rows)
	-- Test full scan on a data set with some deleted rows (the "even" ones).
	-- ALL MODES - TABLE IS, "scan page lockname of form (pageno, 1)
	-- SERIALIZABLE - row S locks as it visits each row, row/table lock
	-- held until end xact. Also holds one previous key lock.
	-- REPEATABLE READ - row S locks as it visits each row, row/table locks held
	-- until end of transaction.
	-- READ COMMITTED - instantaneous S locks requested on each row. No locks
	-- held after query finishes.
	-- READ UNCOMMITTED - no row locks. No locks held after query finishes.
	--
	-- After the delete the base table should look like:
	-- 1, 10, 'one'
	-- 3, 30, 'three'
	-- 5, 50, 'five'
	-- 7, 70, 'seven'
	--------------------------------------------------------------------------------
	delete from a where a = 2 or a = 4 or a = 6;
	commit;

	-- The above delete and commit can allow post commit processing to run before
	-- the subsequent select is run, most likely if this run is on a table where
	-- there is one row per page. To get reproducible results from the subsequent
	-- lock calls, wait for post commit to finish before running the select.
	-- Without this wait "extra" locks sometimes might show up - these are locks
	-- on the committed deleted rows which have not been cleaned yet depending
	-- on timing.
	CALL WAIT_FOR_POST_COMMIT();

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

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


	--------------------------------------------------------------------------------
	-- TEST 2: btree scan, (scan with "<" qualifier)
	-- Test "less than" qualified scan on a data set.
	-- ALL MODES - TABLE IS, "scan page lockname of form (pageno, 1)
	-- SERIALIZABLE - row S locks as it visits each row, row/table lock
	-- held until end xact. Also holds one previous key lock.
	-- REPEATABLE READ - row S locks as it visits each row, row/table locks held
	-- until end of transaction.
	-- READ COMMITTED - instantaneous S locks requested on each row. No locks
	-- held after query finishes.
	-- READ UNCOMMITTED - no row locks. No locks held after query finishes.
	--
	-- At this point the base table should look like:
	-- 1, 10, 'one'
	-- 3, 30, 'three'
	-- 5, 50, 'five'
	-- 7, 70, 'seven'
	--------------------------------------------------------------------------------

	select a from a where a < 3;
	select * from lock_table order by tabname, type desc, mode, cnt, lockname;
	commit;

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

	--------------------------------------------------------------------------------
	-- TEST 3: btree scan, (scan with equals qualifier)
	-- Test "equals" qualified cursor scan on a data set.
	-- ALL MODES - TABLE IS, "scan page lockname of form (pageno, 1)
	-- SERIALIZABLE - row S locks as it visits each row, row/table lock
	-- held until end xact. Also holds one previous key lock.
	-- REPEATABLE READ - row S locks as it visits each row, row/table locks held
	-- until end of transaction.
	-- READ COMMITTED - instantaneous S locks requested on each row. No locks
	-- held after query finishes.
	-- READ UNCOMMITTED - no row locks. No locks held after query finishes.
	--
	-- At this point the base table should look like:
	-- 1, 10, 'one'
	-- 3, 30, 'three'
	-- 5, 50, 'five'
	-- 7, 70, 'seven'
	--------------------------------------------------------------------------------

	select a from a where a = 5;
	select * from lock_table order by tabname, type desc, mode, cnt, lockname;
	commit;

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

	--------------------------------------------------------------------------------
	-- TEST 4: btree scan, (equals qualifier, no rows return)
	-- Test "equals" qualified scan on a data set, no rows returned.
	-- ALL MODES - TABLE IS, "scan page lockname of form (pageno, 1)
	-- SERIALIZABLE - row S locks as it visits each row, row/table lock
	-- held until end xact. Also holds one previous key lock.
	-- REPEATABLE READ - row S locks as it visits each row, row/table locks held
	-- until end of transaction.
	-- READ COMMITTED - instantaneous S locks requested on each row. No locks
	-- held after query finishes.
	-- READ UNCOMMITTED - no row locks. No locks held after query finishes.
	--
	-- At this point the base table should look like:
	-- 1, 10, 'one'
	-- 3, 30, 'three'
	-- 5, 50, 'five'
	-- 7, 70, 'seven'
	--------------------------------------------------------------------------------

	-- no rows expected to qualify
	select a from a where a = 42;
	select * from lock_table order by tabname, type desc, mode, cnt, lockname;
	commit;

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

	drop table a;