updatebtreeholdcursorlocksjdbc30.subsql

derby database source code.good for you.

-- Very basic single user testing of btree cursor update locks.  This ".subsql"
-- test is meant to be run from another test such that it gets run under 
-- multiple isolation levels.  This is important for update locks as they behave
-- differently, depending on isolation levels.
--
-- This test concentrates on updates which use a primary index for the cursor,
-- and then update a non-key field, or delete a row.
--
-- assume's caller has already done: run 'LockTableQuery.subsql'; to get 
-- easy access to the lock VTI.

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, d somesortofpad);
--     create index a_idx on (a, somesortofpad) 
-- 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 full cursor scan which does no updates.
--------------------------------------------------------------------------------
get with hold cursor scan_cursor as
    'select a, b, c from a where a >= 1 and a < 20 for update of b, c';

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

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

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

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

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

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

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

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

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

close scan_cursor;
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 full cursor scan which does no updates, which exits in middle of scan.
--------------------------------------------------------------------------------
get with hold cursor scan_cursor as
    'select a, b, c from a where a >= 1 and a < 20 for update of b, c';

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

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

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

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

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

close scan_cursor;
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 full cursor scan which deletes "even" rows.
--------------------------------------------------------------------------------
get with hold cursor scan_cursor as
    'select a, b, c from a where a >= 1 and a < 20 for update of b, c';

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

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

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

delete from a where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

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

next scan_cursor;
delete from a where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

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

next scan_cursor;
delete from a where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

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

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

close scan_cursor;
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;
select a from a;
select * from a;
commit;


--------------------------------------------------------------------------------
-- Test full cursor scan which does no updates, now there are committed
-- deleted rows in the heap.
-- 
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------
get with hold cursor scan_cursor as
    'select a, b, c from a where a >= 1 and a < 20 for update of b, c';

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

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

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

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

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

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

close scan_cursor;
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;
select * from a;
commit;

--------------------------------------------------------------------------------
-- Test exact match cursor scan does one update, 
-- 
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------
get with hold cursor scan_cursor as
    'select a, b, c from a where a = 3 for update of b, c';

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

next scan_cursor;
update a set b=-3000 where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

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

close scan_cursor;
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;
select * from a;
commit;

--------------------------------------------------------------------------------
-- Test exact match cursor scan does one update, and bales early. 
-- 
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, -3000, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------
get with hold cursor scan_cursor as
    'select a, b, c from a where a = 3 for update of b, c';

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

next scan_cursor;
update a set b=30 where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

close scan_cursor;
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;
select * from a;
commit;

--------------------------------------------------------------------------------
-- Test full cursor scan which updates the middle 2 rows, now there are 
-- committed deleted rows in the heap.
-- 
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, 30, 'three'
-- 5, 50, 'five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------
get with hold cursor scan_cursor as
    'select a, b, c from a where a >= 1 and a < 20 for update of b, c';

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

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

next scan_cursor;
update a set b=-30,c='-three' where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
update a set b=-50,c='-five' where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

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

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

close scan_cursor;
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;
select * from a;
commit;

--------------------------------------------------------------------------------
-- Test qualified full cursor scan which does no updates. 
-- 
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, -30, '-three'
-- 5, -50, '-five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------
get with hold cursor scan_cursor as
    'select a from a where a > 0 and b < 0 for update of b, c';

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

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

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

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

close scan_cursor;
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;
select * from a;
commit;

--------------------------------------------------------------------------------
-- Test qualified exact match cursor scan which deletes one row.
-- 
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, -30, '-three'
-- 5, -50, '-five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------
get with hold cursor scan_cursor as
    'select a from a where a = 1 for update of b, c';

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

next scan_cursor;
delete from a where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

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

close scan_cursor;
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;
select * from a;
commit;

--------------------------------------------------------------------------------
-- Test qualified full cursor scan which deletes the positive rows.
-- 
-- At this point the table should look like:
-- 1, 10, 'one'
-- 3, -30, '-three'
-- 5, -50, '-five'
-- 7, 70, 'seven'
--------------------------------------------------------------------------------
get with hold cursor scan_cursor as
    'select a from a where a <> 3 and a <> 5 for update of b, c';

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

next scan_cursor;
delete from a where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

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

close scan_cursor;
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;
select * from a;
commit;

--------------------------------------------------------------------------------
-- Test qualified full cursor scan which updates a row.
-- 
-- At this point the table should look like:
-- 3, -30, '-three'
-- 5, -50, '-five'
--------------------------------------------------------------------------------

get with hold cursor scan_cursor as
    'select a,b,c from a where a > 2 and a <= 5 for update of b, c';

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

next scan_cursor;
update a set b=30,c='three' where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

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

close scan_cursor;
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;
select * from a;
commit;

--------------------------------------------------------------------------------
-- Test qualified full cursor scan which updates a row and deletes a row.
-- 
-- At this point the table should look like:
-- 3, 30, 'three'
-- 5, -50, '-five'
--------------------------------------------------------------------------------

get with hold cursor scan_cursor as
    'select a, b, c from a where a > 0 for update of b, c';

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

next scan_cursor;
update a set b=3030,c='threethree' where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

next scan_cursor;
delete from a where current of scan_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

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

-------------------------------------------------------------------------------
-- At this point the table should look like:
-- 3, 3030, 'threethree'
--------------------------------------------------------------------------------
select * from a;
commit;

drop table a;