readsetlocks.subsql

derby database source code.good for you.

-- Very basic single user testing of read locks in "set" queries on heap tables.  
-- 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: heap scan.
-- TEST  1: heap scan, some rows deleted.
-- TEST  2: heap scan, (scan with "<" qualifier)
-- TEST  3: heap scan, (scan with equals qualifier)
-- TEST  4: heap scan, (equals qualifier, no rows return)


autocommit off;

--------------------------------------------------------------------------------
-- Assumes that calling routine has set up the following simple dataset, 
-- a heap, no indexes with following initial values:
--     create table (a int, b int, c somesortofchar);
-- 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: heap scan.
-- Test full cursor scan which does no updates.
--    SERIALIZABLE     - will get table level S lock, held to end of xact.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                         (including deleted ones).  held to end of xact.
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release. No
--                         locks held when statement completes.
--    READ UNCOMMITTED - TABLE IS, no row locks.  No locks after statement ends.
--------------------------------------------------------------------------------

select a, b, c 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: heap scan, some rows deleted.
-- Test full cursor scan on a data set with some deleted rows (the "even" ones).
--    SERIALIZABLE     - will get table level S lock.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                         (including deleted ones).
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release
--    READ UNCOMMITTED - TABLE IS, no row locks.
--    SERIALIZABLE     - will get table level S lock, held to end of xact.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                         (including deleted ones).  held to end of xact.
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release. No
--                         locks held when statement completes.
--    READ UNCOMMITTED - TABLE IS, no row locks.  No locks after statement ends.
--
-- After the delete the 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;

select a, b, c 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: heap scan, (scan with "<" qualifier)
-- Test "less than" qualified cursor scan on a data set.
--    SERIALIZABLE     - will get table level S lock, held to end of xact.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                         (including deleted ones).  held to end of xact.
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release. No
--                         locks held when statement completes.
--    READ UNCOMMITTED - TABLE IS, no row locks.  No locks after statement ends.
--
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------

select a, b, c 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 3: heap scan, (scan with equals qualifier)
-- Test "equals" qualified cursor scan on a data set.
--    SERIALIZABLE     - will get table level S lock, held to end of xact.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                         (including deleted ones).  held to end of xact.
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release. No
--                         locks held when statement completes.
--    READ UNCOMMITTED - TABLE IS, no row locks.  No locks after statement ends.
--
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------

select a, b, c 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: heap scan, (equals qualifier, no rows return)
-- Test "equals" qualified cursor scan on a data set, no rows returned.
--    SERIALIZABLE     - will get table level S lock, held to end of xact.
--    REPEATABLE READ  - TABLE IS, will get row S locks as it visits each row 
--                         (including deleted ones).  held to end of xact.
--    READ COMMITTED   - TABLE IS, will get instantaneous locks and release. No
--                         locks held when statement completes.
--    READ UNCOMMITTED - TABLE IS, no row locks.  No locks after statement ends.
--
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------

select a, b, c from a where a = 7;

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;