java/testing/org/apache/derbyTesting/functionTests/tests/store/rlliso2multi.sql

--
--   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 under isolation level 2.  default isolation
-- level has been set as a property to serializable.
--------------------------------------------------------------------------------
run resource '/org/apache/derbyTesting/functionTests/tests/store/createTestProcedures.subsql';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');

autocommit off;

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

--------------------------------------------------------------------------------
-- Test 0: verify isolation level by seeing if a read lock is released or not.
--------------------------------------------------------------------------------
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;

connect 'wombat' as writer;
-- 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;
autocommit off;
create table test_0 (a int);
insert into test_0 values (1);
commit;

set connection writer;
autocommit off;

-- isolation 2 scanner should release read lock on table after statement.
set connection scanner;
select * from test_0;

-- writer should be able to insert into table - scanner released read lock.
set connection writer;
insert into test_0 values (2);

-- scanner will now block on uncommitted insert, and get lock timeout
set connection scanner;
select * from test_0;
commit;

-- commit writer - releasing all locks.
set connection writer;
commit;

-- scanner will now see 2 rows
set connection scanner;
select * from test_0;
commit;

-- cleanup
set connection scanner;
drop table test_0;
commit;
disconnect;
set connection writer;
disconnect;


--------------------------------------------------------------------------------
-- Test 1: make sure a leaf root growing get's the right lock.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- Test setup - create a 1 page btree, with the page ready to split.
--------------------------------------------------------------------------------
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;

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

set connection scanner;
autocommit off;
create table a (a varchar(1200), b varchar(1000)) ;
insert into a values (PADSTRING('a',1200), PADSTRING('a',1000));
insert into a values (PADSTRING('b',1200), PADSTRING('b',1000));
insert into a values (PADSTRING('c',1200), PADSTRING('c',1000));
create index a_idx on a (a) ;
commit;

set connection rootgrower;
autocommit off;
commit;


--------------------------------------------------------------------------------
-- Set up scanner to be doing a row locked covered scan on the index.
--------------------------------------------------------------------------------
set connection scanner;
autocommit off;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor as 
    'select a from a where a >= PADSTRING(''a'',1200) and a < PADSTRING(''c'',1200) ';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
next scan_cursor;

--------------------------------------------------------------------------------
-- Before DERBY-2991 the attempt to split the root would time out because the
-- scan had locked the first page.
--------------------------------------------------------------------------------
set connection rootgrower;
autocommit off;
insert into a values (PADSTRING('d',1200), PADSTRING('d',1000));
rollback;

--------------------------------------------------------------------------------
-- The scan should continue unaffected.
--------------------------------------------------------------------------------
set connection scanner;
next scan_cursor;
next scan_cursor;

--------------------------------------------------------------------------------
-- This insert will block on the previous key lock of the scanner.
--------------------------------------------------------------------------------
set connection rootgrower;
insert into a values (PADSTRING('ab',1200), PADSTRING('ab',1000));

--------------------------------------------------------------------------------
-- Now the grow root should be allowed (note that cursor scan has locks
-- on the leaf page being grown - just not the scan lock).
-- (Scan locks are no longer used after DERBY-2991.)
--------------------------------------------------------------------------------
set connection rootgrower;
insert into a values (PADSTRING('d',1200), PADSTRING('d',1000));

select a from a;


--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection rootgrower;
commit;
disconnect;
set connection scanner;
commit;
drop table a;
commit;
disconnect;


--------------------------------------------------------------------------------
-- Test 2: make sure previous key locks are gotten correctly.
--------------------------------------------------------------------------------
connect 'wombat' as client_1;
-- 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 client_2;
-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;

set connection client_1;
autocommit off;
create table a (a varchar(1000), b varchar(1000)) ;
create unique index a_idx on a (a) ;
insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));
insert into a values (PADSTRING('c',1000), PADSTRING('c',1000));
insert into a values (PADSTRING('e',1000), PADSTRING('e',1000));
insert into a values (PADSTRING('f',1000), PADSTRING('f',1000));
insert into a values (PADSTRING('g',1000), PADSTRING('g',1000));
commit;

set connection client_2;
autocommit off;

--------------------------------------------------------------------------------
-- client 1 will get exclusive locks on 'c'.
--------------------------------------------------------------------------------
set connection client_1;
update a set b = 'new value' where a > 'b' and a <= 'd';

-- run resource '/org/apache/derbyTesting/functionTests/tests/store/LockTableQuery.subsql';

set connection client_2;

--------------------------------------------------------------------------------

-- the following will not time out, the insert
-- will get a previous key insert lock which will not conflict with the
-- non-insert read-committed exclusive lock on 'c'.

--------------------------------------------------------------------------------
insert into a values (PADSTRING('d',1000), PADSTRING('d',1000));

--------------------------------------------------------------------------------
-- the following should NOT cause a time out
--------------------------------------------------------------------------------
insert into a values (PADSTRING('a',1000), PADSTRING('a',1000));

--------------------------------------------------------------------------------
-- the following will block because it is a unique index, and the insert is of
-- the same row being locked by client_1
--------------------------------------------------------------------------------
insert into a values (PADSTRING('c',1000), PADSTRING('c',1000));

-- run resource '/org/apache/derbyTesting/functionTests/tests/store/LockTableQuery.subsql';

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection client_1;
select * from a;
commit;
set connection client_2;
commit;
select * from a;
drop table a;
commit;

--------------------------------------------------------------------------------
-- Test 3: make sure an exact key insert into unique key index blocks.
--------------------------------------------------------------------------------
set connection client_1;
autocommit off;
create table a (a varchar(1000), b varchar(1000)) ;
create unique index a_idx on a (a) ;
commit;
insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));

set connection client_2;
autocommit off;
--------------------------------------------------------------------------------
-- the following should cause a time out, as the previous
-- key lock will conflict with client_1's lock on 'b'
--------------------------------------------------------------------------------
insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));

--------------------------------------------------------------------------------
-- Test 4: make sure that row lock wait in a heap scan works
--------------------------------------------------------------------------------
set connection client_1;
autocommit off;
create table test_4 (a int, b varchar(1000), c varchar(1000)) ;
commit;
set connection client_2;
autocommit off;
commit;

-- client_1 will get a single row lock in the heap.
set connection client_1;
insert into test_4 values (1, PADSTRING('a',1000), PADSTRING('b',1000)); 

-- client_2 scans table, blocking on a row lock on the client_1 insert row, 
-- will get timeout message.
set connection client_2;
select * from test_4;

-- release the insert lock.
set connection client_1;
commit;

-- reader should be able to see row now.
set connection client_2;
select * from test_4;
commit;

-- cleanup
set connection client_1;
drop table test_4;
commit;

--------------------------------------------------------------------------------
-- Test 5: make sure a that a group fetch through a secondary index correctly
--         handles a row that is deleted after it has read a row from the index
--         but before it has read the row from the base table.
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- Test setup - create a 1 page btre, with the page ready to split.
--------------------------------------------------------------------------------
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;

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;


set connection scanner;
autocommit off;
create table test_5 (a int, a2 int, b varchar(1000), c varchar(1000)) ;
insert into test_5 values (1, 10, PADSTRING('a',1000), PADSTRING('a',1000));
insert into test_5 values (2, 20, PADSTRING('b',1000), PADSTRING('b',1000));
insert into test_5 values (3, 30, PADSTRING('c',1000), PADSTRING('c',1000));
insert into test_5 values (4, 40, PADSTRING('d',1000), PADSTRING('d',1000));
insert into test_5 values (5, 50, PADSTRING('e',1000), PADSTRING('e',1000));
insert into test_5 values (6, 60, PADSTRING('f',1000), PADSTRING('f',1000));
create index test_5_idx on test_5 (a);
commit;

set connection deleter;
autocommit off;
commit;


--------------------------------------------------------------------------------
-- Set up scanner to be doing a row locked index to base row scan on the index.
-- By using group fetch it will read and release locks on multiple rows from
-- the index and save away row pointers from the index.
--------------------------------------------------------------------------------
set connection scanner;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','10');
get cursor scan_cursor as 
    'select a, a2 from test_5 where a > 1 ';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

-- because of group locking will get locks on 1, 2, 3, 4, and 5 and then will
-- release the locks on 1, 2, 3, and 4.  The last one is released on close or
-- on next call emptying the cursor.
next scan_cursor;

--------------------------------------------------------------------------------
-- Delete a row that the scanner has looked at but not reported back to the
-- caller.
--------------------------------------------------------------------------------
set connection deleter;
delete from test_5 where a = 4;

--------------------------------------------------------------------------------
-- The scan will requalify rows when it goes to the base table, thus it will
-- see 3, but block when it gets to the key of deleted row (4).
--------------------------------------------------------------------------------
set connection scanner;
next scan_cursor;
next scan_cursor;

-- commit the delete
set connection deleter;
commit;

-- scanner should see 1,2,3,4,6
set connection scanner;
close scan_cursor;

select a,b from test_5;

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection deleter;
commit;
disconnect;
set connection scanner;
commit;
drop table test_5;
commit;
disconnect;


--------------------------------------------------------------------------------
-- Test 6: make sure a that heap scans which cross page boundaries release
--         locks correctly.
--------------------------------------------------------------------------------

-- Test setup - create a heap with one row per page.

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;

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;


set connection scanner;
autocommit off;
create table test_6 (a int, a2 int, b varchar(2000), c varchar(2000)) ;
insert into test_6 values (1, 10, PADSTRING('a',2000), PADSTRING('a',2000));
insert into test_6 values (2, 20, PADSTRING('b',2000), PADSTRING('b',2000));
insert into test_6 values (3, 30, PADSTRING('c',2000), PADSTRING('c',2000));
insert into test_6 values (4, 40, PADSTRING('d',2000), PADSTRING('d',2000));
insert into test_6 values (5, 50, PADSTRING('e',2000), PADSTRING('e',2000));
create index test_6_idx on test_6 (a);
commit;

set connection deleter;
autocommit off;
commit;


--------------------------------------------------------------------------------
-- Set up scanner to be doing a row locked index to base row scan on the index.
-- By using group fetch it will read and release locks on multiple rows from
-- the index and save away row pointers from the index.
--------------------------------------------------------------------------------
set connection scanner;
get cursor scan_cursor as 
    'select a, a2 from test_6';

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

--------------------------------------------------------------------------------
-- Delete all rows that the scanner has looked at, and should have released the
-- lock on.
--------------------------------------------------------------------------------
set connection deleter;
delete from test_6 where a = 1;
delete from test_6 where a = 2;
delete from test_6 where a = 3;
delete from test_6 where a = 4;

--------------------------------------------------------------------------------
-- The scan should either block on the delete or continue and not return the
-- the deleted row.
--------------------------------------------------------------------------------
set connection scanner;
next scan_cursor;
close scan_cursor;

-- commit the delete
set connection deleter;
delete from test_6 where a = 5;
commit;

-- scanner should see no rows.
set connection scanner;
select a,b from test_6;

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection deleter;
commit;
disconnect;
set connection scanner;
commit;
drop table test_6;
commit;
disconnect;


--------------------------------------------------------------------------------
-- Test 7: make sure that 2 heap cursor scans in same transaction work (at one
--         point there was a problem where releasing locks in one of the cursors
--         released locks in the other cursor).
--------------------------------------------------------------------------------

-- Test setup - create a heap with one row per page.

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;

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;


--------------------------------------------------------------------------------
-- HEAP SCAN
--------------------------------------------------------------------------------
set connection scanner;
autocommit off;
create table test_7 (a int, a2 int, b varchar(2000), c varchar(2000)) ;
insert into test_7 values (1, 10, PADSTRING('a',2000), PADSTRING('a',2000));
insert into test_7 values (2, 20, PADSTRING('b',2000), PADSTRING('b',2000));
insert into test_7 values (3, 30, PADSTRING('c',2000), PADSTRING('c',2000));
insert into test_7 values (4, 40, PADSTRING('d',2000), PADSTRING('d',2000));
insert into test_7 values (5, 50, PADSTRING('e',2000), PADSTRING('e',2000));
commit;

set connection deleter;
autocommit off;
commit;


-- Set up scanner to be doing a row locked heap scan, going one row at a time. 
set connection scanner;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor_1 as 
    'select a, a2 from test_7';
get cursor scan_cursor_2 as 
    'select a, a2 from test_7';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');

next scan_cursor_1;
next scan_cursor_1;
next scan_cursor_1;
next scan_cursor_1;
next scan_cursor_1;

next scan_cursor_2;

close scan_cursor_2;

-- Get exclusive table lock on test_7.  Should fail with table cannot be locked.
set connection deleter;
lock table test_7 in exclusive mode;

-- release all read locks, by moving the cursor past all the rows.
set connection scanner;
next scan_cursor_1;
close scan_cursor_1;

-- Get exclusive table lock on test_7.  Now that both scan closed this should
-- work.
set connection deleter;
delete from test_7;
commit;

-- scanner should see no rows.
set connection scanner;
select a,b from test_7;
commit;

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection deleter;
commit;
disconnect;
set connection scanner;
commit;
drop table test_7;
commit;
disconnect;

--------------------------------------------------------------------------------
-- Test 8: Exercise post commit cases, force the code through the path, no easy
--         way to make sure the post commit work is actually doing something.
--         All these case were run with lock debugging by hand to make sure the
--         right thing was happening:
--         
--         8.1 - heap post commit successfully gets table X lock and cleans up.
--         8.2 - heap post commit can't get table X lock, so gives up and let's
--               client continue on with work.
--         8.3 - btree post commit successfully gets table X lock and cleans up.
--         8.4 - btree post commit can't get table X lock, so gives up and let's
--               client continue on with work.
--               client continue on with work.
--
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- 8.1 setup 
--------------------------------------------------------------------------------
set connection client_1;
create table test_8 (a int, a2 int, b varchar(2000), c char(10)) 
    ;
create index test_8_idx on test_8 (a);
insert into test_8 values (1, 10, PADSTRING('a',2000), 'test 8.1');
insert into test_8 values (2, 20, PADSTRING('b',2000), 'test 8.1');
insert into test_8 values (3, 30, PADSTRING('c',2000), 'test 8.1');
insert into test_8 values (4, 40, PADSTRING('d',2000), 'test 8.1');
insert into test_8 values (5, 50, PADSTRING('e',2000), 'test 8.1');
commit;

--------------------------------------------------------------------------------
-- 8.1 test - simply delete rows from table, heap post commit will run and 
--            reclaim all pages.
--------------------------------------------------------------------------------

set connection client_1;
delete from test_8;
commit;
select a from test_8;
commit;

--------------------------------------------------------------------------------
-- 8.2 setup 
--------------------------------------------------------------------------------
drop table test_8;
create table test_8 (a int, a2 int, b varchar(1000), c char(10))
    ;
create index test_8_idx on test_8 (a);
insert into test_8 values (1, 10, PADSTRING('a',1000), 'test 8.2');
insert into test_8 values (2, 20, PADSTRING('b',1000), 'test 8.2');
insert into test_8 values (3, 30, PADSTRING('c',1000), 'test 8.2');
insert into test_8 values (4, 40, PADSTRING('d',1000), 'test 8.2');
insert into test_8 values (5, 50, PADSTRING('e',1000), 'test 8.2');
commit;

--------------------------------------------------------------------------------
-- 8.2 test - client 1 holds row lock which will prevent client 2 post commit.
--------------------------------------------------------------------------------

set connection client_1;
insert into test_8 values (6, 60, PADSTRING('a',1000), 'test 8.2');

set connection client_2;
delete from test_8 where a < 5;
commit;

set connection client_1;
select a from test_8;
commit;

set connection client_2;
select a from test_8;
commit;

--------------------------------------------------------------------------------
-- 8.3 setup 
--------------------------------------------------------------------------------
drop table test_8;
create table test_8 (a int, a2 int, b varchar(1500), c char(10));
create index test_8_idx on test_8 (a, b)
    ;
insert into test_8 values (1, 10, PADSTRING('a',1500), 'test 8.3');
insert into test_8 values (2, 20, PADSTRING('b',1500), 'test 8.3');
insert into test_8 values (3, 30, PADSTRING('c',1500), 'test 8.3');
insert into test_8 values (4, 40, PADSTRING('d',1500), 'test 8.3');
insert into test_8 values (5, 50, PADSTRING('e',1500), 'test 8.3');
commit;

--------------------------------------------------------------------------------
-- 8.3 test - simply delete rows from index table, btree post commit will run
--            and reclaim all pages.
--------------------------------------------------------------------------------

set connection client_1;
delete from test_8;
commit;
select a from test_8;
commit;

--------------------------------------------------------------------------------
-- 8.4 setup 
--------------------------------------------------------------------------------
drop table test_8;
create table test_8 (a int, a2 int, b varchar(1500), c char(10)) ;
create index test_8_idx1 on test_8 (a);
create index test_8_idx2 on test_8 (a, b)
    ;
insert into test_8 values (1, 10, PADSTRING('a',1500), 'test 8.4');
insert into test_8 values (2, 20, PADSTRING('b',1500), 'test 8.4');
insert into test_8 values (3, 30, PADSTRING('c',1500), 'test 8.4');
insert into test_8 values (4, 40, PADSTRING('d',1500), 'test 8.4');
insert into test_8 values (5, 50, PADSTRING('e',1500), 'test 8.4');
commit;

--------------------------------------------------------------------------------
-- 8.4 test - client 1 holds row lock which will prevent client 2 post commit.
--------------------------------------------------------------------------------

set connection client_1;
insert into test_8 values (6, 60, PADSTRING('a',1500), 'test 8.4');

set connection client_2;
delete from test_8 where a < 5;
commit;

set connection client_1;
select a from test_8;
commit;

set connection client_2;
select a from test_8;
commit;

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------

set connection client_1;
drop table test_8;
commit;
disconnect;

set connection client_2;
commit;
disconnect;

--------------------------------------------------------------------------------
-- Test 9: Make sure scan positioning in the beginning of a unique scan
--         properly gets the scan lock to block with splits.
--         (Scan locks are no longer used after DERBY-2991.)
--
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-- 9.1 setup 
--------------------------------------------------------------------------------
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;
autocommit off;
commit;

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

set connection scanner;
create table test_9 (a int, a2 int, b varchar(1000), c char(10)) 
    ;
insert into test_9 values (5, 50, PADSTRING('e',1000), 'test 9.1');
insert into test_9 values (4, 40, PADSTRING('d',1000), 'test 9.1');
insert into test_9 values (3, 30, PADSTRING('c',1000), 'test 9.1');
insert into test_9 values (2, 20, PADSTRING('b',1000), 'test 9.1');
insert into test_9 values (1, 10, PADSTRING('a',1000), 'test 9.1');
create unique index test_9_idx on test_9 (b) ;
commit;

--------------------------------------------------------------------------------
-- 9.1 test - open a cursor for update on table, and make sure splitter waits
--            on the scan position.
--------------------------------------------------------------------------------

set connection scanner;
CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
get cursor scan_cursor as
    'select b from test_9 where b >= ''a'' ';
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
next scan_cursor;
next scan_cursor;

-- the following will get a couple of rows and then block on the split.
set connection splitter;
insert into test_9 values (0, 10, PADSTRING('aa',1000), 'test 9.1');
commit;
insert into test_9 values (0, 10, PADSTRING('ab',1000), 'test 9.1');
commit;

-- insert ahead in the cursor to make sure we pick it up later.
-- This would time out before DERBY-2991.
insert into test_9 values (0, 10, PADSTRING('dd',1000), 'test 9.1');
rollback;

set connection scanner;
next scan_cursor;
next scan_cursor;
next scan_cursor;
next scan_cursor;
commit;


--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------

set connection scanner;
drop table test_9;
commit;
disconnect;

set connection splitter;
commit;
disconnect;

--------------------------------------------------------------------------------
-- Test 10: Make sure a ddl does not block the lock table vti.
--------------------------------------------------------------------------------

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

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

set connection ddl;
run resource '/org/apache/derbyTesting/functionTests/tests/store/LockTableQuery.subsql';
commit;



--------------------------------------------------------------------------------
-- 10 test - do ddl in one connection and look at lock table in another 
--           connection.
--------------------------------------------------------------------------------

set connection ddl;

create table test_10 (a int, a2 int, b varchar(1000), c char(10)) 
    ;
insert into test_10 values (4, 40, PADSTRING('d',1000), 'test 9.1');
insert into test_10 values (3, 30, PADSTRING('c',1000), 'test 9.1');
insert into test_10 values (2, 20, PADSTRING('b',1000), 'test 9.1');
insert into test_10 values (1, 10, PADSTRING('a',1000), 'test 9.1');

set connection locktable;
-- this should not block on the other thread.
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
commit;

--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------

set connection ddl;
drop table test_10;
commit;
disconnect;

set connection locktable;
commit;
disconnect;



--------------------------------------------------------------------------------
-- Test 11: test update locks
--------------------------------------------------------------------------------

connect 'wombat' as t11scanner;
-- 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 t11updater;
-- 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 t11writer;
-- 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 t11updater;
autocommit off;
create table test_11 (a int, b int);
insert into test_11 values (1,1);
insert into test_11 values (2,2);
insert into test_11 values (8,8);
create index test11_idx on test_11 (a);
commit;

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

--
--==================================================
-- t11updater gets an update lock on row where a=1
--==================================================

get cursor update_cursor as
    'select b from test_11 where a=1 for update of b';

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

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

set connection t11scanner;
autocommit off;

--
--------------------------------------------------
-- try to scan the table, readers are compatible with update lock.
--------------------------------------------------
select * from test_11;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

--
--------------------------------------------------
-- try to update the table, should timeout
--------------------------------------------------
update test_11 set b=99 where a = 1;

--
--------------------------------------------------
-- try to update the table, should go through
--------------------------------------------------
update test_11 set b=99 where a = 8;
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;

--
--------------------------------------------------
-- try to get an update lock
--------------------------------------------------
get cursor update_cursor2 as
    'select b from test_11 where a=1 for update of b';
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

--
--------------------------------------------------
-- should timeout (other transaction has a shared lock on this row)
--------------------------------------------------
next update_cursor2;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

--
--------------------------------------------------
-- should succeed (no other transaction has a shared lock on this row)
--------------------------------------------------
get cursor update_cursor3 as
    'select b from test_11 where a=8 for update of b';
select type, cnt, mode, tabname, lockname, state from lock_table2 order by tabname, type desc, mode, cnt, lockname;

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

commit;
close update_cursor2;
close update_cursor3;

set connection t11updater;
commit;
close update_cursor;

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

--
--==================================================
-- t11scanner gets a read lock
--==================================================

select b from test_11 where a=1;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;


--
--------------------------------------------------
-- should succeed (can get an update lock if there is already a shared lock)
--------------------------------------------------
set connection t11updater;
get cursor update_cursor as
    'select b from test_11 where a=1 for update of b';
next update_cursor;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

commit;
close update_cursor;

set connection t11scanner;
commit;

--
--==================================================
-- t11writer gets a write lock
--==================================================

set connection t11writer;
autocommit off;
update test_11 set b=77 where a=2;
select * from lock_table order by tabname, type desc, mode, cnt, lockname;

set connection t11updater;
get cursor update_cursor as
    'select b from test_11 where a=2 for update of b';
select * from lock_table order by tabname, type desc, mode, cnt, lockname;
--
----------------------------------------------------
-- should timeout
----------------------------------------------------
next update_cursor;


--------------------------------------------------------------------------------
-- cleanup.
--------------------------------------------------------------------------------
set connection t11updater;
close update_cursor;
drop table test_11;
commit;
disconnect;
set connection t11scanner;
disconnect;
set connection t11writer;
disconnect;


exit;