db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From kmars...@apache.org
Subject svn commit: r109565 - in incubator/derby/code/trunk/java/testing: . org/apache/derbyTesting/functionTests/master org/apache/derbyTesting/functionTests/suites
Date Thu, 02 Dec 2004 21:59:43 GMT
Author: kmarsden
Date: Thu Dec  2 13:59:42 2004
New Revision: 109565

URL: http://svn.apache.org/viewcvs?view=rev&rev=109565
Log:
Test master update and removal of tests that do not exist from suites.


Added:
   incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/rlliso2multi.out
Modified:
   incubator/derby/code/trunk/java/testing/build.xml
   incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
   incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storemore.runall

Modified: incubator/derby/code/trunk/java/testing/build.xml
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/build.xml?view=diff&rev=109565&p1=incubator/derby/code/trunk/java/testing/build.xml&r1=109564&p2=incubator/derby/code/trunk/java/testing/build.xml&r2=109565
==============================================================================
--- incubator/derby/code/trunk/java/testing/build.xml	(original)
+++ incubator/derby/code/trunk/java/testing/build.xml	Thu Dec  2 13:59:42 2004
@@ -45,6 +45,7 @@
     <ant dir="${derby.testing.src.dir}/${derby.testing.functest.dir}/tests/jdbcapi"/> 
     <ant dir="${derby.testing.src.dir}/${derby.testing.functest.dir}/tests/lang"/> 
     <ant dir="${derby.testing.src.dir}/${derby.testing.functest.dir}/tests/store"/> 
+    <ant dir="${derby.testing.src.dir}/${derby.testing.functest.dir}/tests/storetests"/> 
     <ant dir="${derby.testing.src.dir}/${derby.testing.functest.dir}/tests/tools"/> 
     <ant dir="${derby.testing.src.dir}/${derby.testing.functest.dir}/tests/derbynet"/> 
     <ant dir="${derby.testing.src.dir}/${derby.testing.functest.dir}/master"/> 

Added: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/rlliso2multi.out
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/rlliso2multi.out?view=auto&rev=109565
==============================================================================
--- (empty file)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/rlliso2multi.out	Thu Dec  2 13:59:42 2004
@@ -0,0 +1,1215 @@
+ij> --------------------------------------------------------------------------------
+-- Test multi user lock interaction under isolation level 2.  default isolation
+-- level has been set as a property to serializable.
+--------------------------------------------------------------------------------
+run resource 'createTestProcedures.subsql';
+ij> CREATE FUNCTION  PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA;
+0 rows inserted/updated/deleted
+ij> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA;
+0 rows inserted/updated/deleted
+ij> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096');
+0 rows inserted/updated/deleted
+ij> autocommit off;
+ij> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij> --------------------------------------------------------------------------------
+-- Test 0: verify isolation level by seeing if a read lock is released or not.
+--------------------------------------------------------------------------------
+connect 'wombat' as scanner;
+ij(SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(SCANNER)> connect 'wombat' as writer;
+ij(WRITER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(WRITER)> -- set up
+set connection scanner;
+ij(SCANNER)> autocommit off;
+ij(SCANNER)> create table test_0 (a int);
+0 rows inserted/updated/deleted
+ij(SCANNER)> insert into test_0 values (1);
+1 row inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> set connection writer;
+ij(WRITER)> autocommit off;
+ij(WRITER)> -- isolation 2 scanner should release read lock on table after statement.
+set connection scanner;
+ij(SCANNER)> select * from test_0;
+A          
+-----------
+1          
+ij(SCANNER)> -- writer should be able to insert into table - scanner released read lock.
+set connection writer;
+ij(WRITER)> insert into test_0 values (2);
+1 row inserted/updated/deleted
+ij(WRITER)> -- scanner will now block on uncommitted insert, and get lock timeout
+set connection scanner;
+ij(SCANNER)> select * from test_0;
+A          
+-----------
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(SCANNER)> commit;
+ij(SCANNER)> -- commit writer - releasing all locks.
+set connection writer;
+ij(WRITER)> commit;
+ij(WRITER)> -- scanner will now see 2 rows
+set connection scanner;
+ij(SCANNER)> select * from test_0;
+A          
+-----------
+1          
+2          
+ij(SCANNER)> commit;
+ij(SCANNER)> -- cleanup
+set connection scanner;
+ij(SCANNER)> drop table test_0;
+0 rows inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> disconnect;
+ij> set connection writer;
+ij(WRITER)> disconnect;
+ij> --------------------------------------------------------------------------------
+-- 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;
+ij(SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(SCANNER)> connect 'wombat' as rootgrower;
+ij(ROOTGROWER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(ROOTGROWER)> set connection scanner;
+ij(SCANNER)> autocommit off;
+ij(SCANNER)> create table a (a varchar(1200), b varchar(1000)) ;
+0 rows inserted/updated/deleted
+ij(SCANNER)> insert into a values (PADSTRING('a',1200), PADSTRING('a',1000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into a values (PADSTRING('b',1200), PADSTRING('b',1000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into a values (PADSTRING('c',1200), PADSTRING('c',1000));
+1 row inserted/updated/deleted
+ij(SCANNER)> create index a_idx on a (a) ;
+0 rows inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> set connection rootgrower;
+ij(ROOTGROWER)> autocommit off;
+ij(ROOTGROWER)> commit;
+ij(ROOTGROWER)> --------------------------------------------------------------------------------
+-- Set up scanner to be doing a row locked covered scan on the index.
+--------------------------------------------------------------------------------
+set connection scanner;
+ij(SCANNER)> autocommit off;
+ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
+0 rows inserted/updated/deleted
+ij(SCANNER)> get cursor scan_cursor as 
+    'select a from a where a >= PADSTRING(''a'',1200) and a < PADSTRING(''c'',1200) ';
+ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
+0 rows inserted/updated/deleted
+ij(SCANNER)> next scan_cursor;
+A                                                                                                                               
+--------------------------------------------------------------------------------------------------------------------------------
+a                                                                                                                              &
+ij(SCANNER)> --------------------------------------------------------------------------------
+-- This should block and timeout on the scan lock held by the scanner on the first page.
+--------------------------------------------------------------------------------
+set connection rootgrower;
+ij(ROOTGROWER)> autocommit off;
+ij(ROOTGROWER)> insert into a values (PADSTRING('d',1200), PADSTRING('d',1000));
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(ROOTGROWER)> --------------------------------------------------------------------------------
+-- The scan should continue unaffected.
+--------------------------------------------------------------------------------
+set connection scanner;
+ij(SCANNER)> next scan_cursor;
+A                                                                                                                               
+--------------------------------------------------------------------------------------------------------------------------------
+b                                                                                                                              &
+ij(SCANNER)> next scan_cursor;
+No current row
+ij(SCANNER)> --------------------------------------------------------------------------------
+-- This insert will block on the previous key lock of the scanner.
+--------------------------------------------------------------------------------
+set connection rootgrower;
+ij(ROOTGROWER)> insert into a values (PADSTRING('ab',1200), PADSTRING('ab',1000));
+1 row inserted/updated/deleted
+ij(ROOTGROWER)> --------------------------------------------------------------------------------
+-- Now the grow root should be allowed (note that cursor scan has locks
+-- on the leaf page being grown - just not the scan lock).
+--------------------------------------------------------------------------------
+set connection rootgrower;
+ij(ROOTGROWER)> insert into a values (PADSTRING('d',1200), PADSTRING('d',1000));
+1 row inserted/updated/deleted
+ij(ROOTGROWER)> select a from a;
+A                                                                                                                               
+--------------------------------------------------------------------------------------------------------------------------------
+a                                                                                                                              &
+ab                                                                                                                             &
+b                                                                                                                              &
+c                                                                                                                              &
+d                                                                                                                              &
+ij(ROOTGROWER)> --------------------------------------------------------------------------------
+-- cleanup.
+--------------------------------------------------------------------------------
+set connection rootgrower;
+ij(ROOTGROWER)> commit;
+ij(ROOTGROWER)> disconnect;
+ij> set connection scanner;
+ij(SCANNER)> commit;
+ij(SCANNER)> drop table a;
+0 rows inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> disconnect;
+ij> --------------------------------------------------------------------------------
+-- Test 2: make sure previous key locks are gotten correctly.
+--------------------------------------------------------------------------------
+connect 'wombat' as client_1;
+ij(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;
+ij(CLIENT_1)> connect 'wombat' as client_2;
+ij(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;
+ij(CLIENT_2)> set connection client_1;
+ij(CLIENT_1)> autocommit off;
+ij(CLIENT_1)> create table a (a varchar(1000), b varchar(1000)) ;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> create unique index a_idx on a (a) ;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into a values (PADSTRING('c',1000), PADSTRING('c',1000));
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into a values (PADSTRING('e',1000), PADSTRING('e',1000));
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into a values (PADSTRING('f',1000), PADSTRING('f',1000));
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into a values (PADSTRING('g',1000), PADSTRING('g',1000));
+1 row inserted/updated/deleted
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> set connection client_2;
+ij(CLIENT_2)> autocommit off;
+ij(CLIENT_2)> --------------------------------------------------------------------------------
+-- client 1 will get exclusive locks on 'c'.
+--------------------------------------------------------------------------------
+set connection client_1;
+ij(CLIENT_1)> update a set b = 'new value' where a > 'b' and a <= 'd';
+1 row inserted/updated/deleted
+ij(CLIENT_1)> -- run resource 'LockTableQuery.subsql';
+set connection client_2;
+ij(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));
+1 row inserted/updated/deleted
+ij(CLIENT_2)> --------------------------------------------------------------------------------
+-- the following should NOT cause a time out
+--------------------------------------------------------------------------------
+insert into a values (PADSTRING('a',1000), PADSTRING('a',1000));
+1 row inserted/updated/deleted
+ij(CLIENT_2)> --------------------------------------------------------------------------------
+-- 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));
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(CLIENT_2)> -- run resource 'LockTableQuery.subsql';
+--------------------------------------------------------------------------------
+-- cleanup.
+--------------------------------------------------------------------------------
+set connection client_1;
+ij(CLIENT_1)> select * from a;
+A                                                                                                                               |B                                                                                                                               
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+b                                                                                                                              &|b                                                                                                                              &
+c                                                                                                                              &|new value                                                                                                                       
+e                                                                                                                              &|e                                                                                                                              &
+f                                                                                                                              &|f                                                                                                                              &
+g                                                                                                                              &|g                                                                                                                              &
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> set connection client_2;
+ij(CLIENT_2)> commit;
+ij(CLIENT_2)> select * from a;
+A                                                                                                                               |B                                                                                                                               
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+b                                                                                                                              &|b                                                                                                                              &
+c                                                                                                                              &|new value                                                                                                                       
+e                                                                                                                              &|e                                                                                                                              &
+f                                                                                                                              &|f                                                                                                                              &
+g                                                                                                                              &|g                                                                                                                              &
+ij(CLIENT_2)> drop table a;
+0 rows inserted/updated/deleted
+ij(CLIENT_2)> commit;
+ij(CLIENT_2)> --------------------------------------------------------------------------------
+-- Test 3: make sure an exact key insert into unique key index blocks.
+--------------------------------------------------------------------------------
+set connection client_1;
+ij(CLIENT_1)> autocommit off;
+ij(CLIENT_1)> create table a (a varchar(1000), b varchar(1000)) ;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> create unique index a_idx on a (a) ;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> insert into a values (PADSTRING('b',1000), PADSTRING('b',1000));
+1 row inserted/updated/deleted
+ij(CLIENT_1)> set connection client_2;
+ij(CLIENT_2)> autocommit off;
+ij(CLIENT_2)> --------------------------------------------------------------------------------
+-- 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));
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(CLIENT_2)> --------------------------------------------------------------------------------
+-- Test 4: make sure that row lock wait in a heap scan works
+--------------------------------------------------------------------------------
+set connection client_1;
+ij(CLIENT_1)> autocommit off;
+ij(CLIENT_1)> create table test_4 (a int, b varchar(1000), c varchar(1000)) ;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> set connection client_2;
+ij(CLIENT_2)> autocommit off;
+ij(CLIENT_2)> commit;
+ij(CLIENT_2)> -- client_1 will get a single row lock in the heap.
+set connection client_1;
+ij(CLIENT_1)> insert into test_4 values (1, PADSTRING('a',1000), PADSTRING('b',1000));
+1 row inserted/updated/deleted
+ij(CLIENT_1)> -- client_2 scans table, blocking on a row lock on the client_1 insert row, 
+-- will get timeout message.
+set connection client_2;
+ij(CLIENT_2)> select * from test_4;
+A          |B                                                                                                                               |C                                                                                                                               
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(CLIENT_2)> -- release the insert lock.
+set connection client_1;
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> -- reader should be able to see row now.
+set connection client_2;
+ij(CLIENT_2)> select * from test_4;
+A          |B                                                                                                                               |C                                                                                                                               
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+1          |a                                                                                                                              &|b                                                                                                                              &
+ij(CLIENT_2)> commit;
+ij(CLIENT_2)> -- cleanup
+set connection client_1;
+ij(CLIENT_1)> drop table test_4;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> --------------------------------------------------------------------------------
+-- 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;
+ij(SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(SCANNER)> connect 'wombat' as deleter;
+ij(DELETER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(DELETER)> set connection scanner;
+ij(SCANNER)> autocommit off;
+ij(SCANNER)> create table test_5 (a int, a2 int, b varchar(1000), c varchar(1000)) ;
+0 rows inserted/updated/deleted
+ij(SCANNER)> insert into test_5 values (1, 10, PADSTRING('a',1000), PADSTRING('a',1000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_5 values (2, 20, PADSTRING('b',1000), PADSTRING('b',1000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_5 values (3, 30, PADSTRING('c',1000), PADSTRING('c',1000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_5 values (4, 40, PADSTRING('d',1000), PADSTRING('d',1000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_5 values (5, 50, PADSTRING('e',1000), PADSTRING('e',1000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_5 values (6, 60, PADSTRING('f',1000), PADSTRING('f',1000));
+1 row inserted/updated/deleted
+ij(SCANNER)> create index test_5_idx on test_5 (a);
+0 rows inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> set connection deleter;
+ij(DELETER)> autocommit off;
+ij(DELETER)> commit;
+ij(DELETER)> --------------------------------------------------------------------------------
+-- 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;
+ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','10');
+0 rows inserted/updated/deleted
+ij(SCANNER)> get cursor scan_cursor as 
+    'select a, a2 from test_5 where a > 1 ';
+ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
+0 rows inserted/updated/deleted
+ij(SCANNER)> -- 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;
+A          |A2         
+-----------------------
+2          |20         
+ij(SCANNER)> --------------------------------------------------------------------------------
+-- Delete a row that the scanner has looked at but not reported back to the
+-- caller.
+--------------------------------------------------------------------------------
+set connection deleter;
+ij(DELETER)> delete from test_5 where a = 4;
+1 row inserted/updated/deleted
+ij(DELETER)> --------------------------------------------------------------------------------
+-- 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;
+ij(SCANNER)> next scan_cursor;
+A          |A2         
+-----------------------
+3          |30         
+ij(SCANNER)> next scan_cursor;
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(SCANNER)> -- commit the delete
+set connection deleter;
+ij(DELETER)> commit;
+ij(DELETER)> -- scanner should see 1,2,3,4,6
+set connection scanner;
+ij(SCANNER)> close scan_cursor;
+ij(SCANNER)> select a,b from test_5;
+A          |B                                                                                                                               
+--------------------------------------------------------------------------------------------------------------------------------------------
+1          |a                                                                                                                              &
+2          |b                                                                                                                              &
+3          |c                                                                                                                              &
+5          |e                                                                                                                              &
+6          |f                                                                                                                              &
+ij(SCANNER)> --------------------------------------------------------------------------------
+-- cleanup.
+--------------------------------------------------------------------------------
+set connection deleter;
+ij(DELETER)> commit;
+ij(DELETER)> disconnect;
+ij> set connection scanner;
+ij(SCANNER)> commit;
+ij(SCANNER)> drop table test_5;
+0 rows inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> disconnect;
+ij> --------------------------------------------------------------------------------
+-- 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;
+ij(SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(SCANNER)> connect 'wombat' as deleter;
+ij(DELETER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(DELETER)> set connection scanner;
+ij(SCANNER)> autocommit off;
+ij(SCANNER)> create table test_6 (a int, a2 int, b varchar(2000), c varchar(2000)) ;
+0 rows inserted/updated/deleted
+ij(SCANNER)> insert into test_6 values (1, 10, PADSTRING('a',2000), PADSTRING('a',2000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_6 values (2, 20, PADSTRING('b',2000), PADSTRING('b',2000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_6 values (3, 30, PADSTRING('c',2000), PADSTRING('c',2000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_6 values (4, 40, PADSTRING('d',2000), PADSTRING('d',2000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_6 values (5, 50, PADSTRING('e',2000), PADSTRING('e',2000));
+1 row inserted/updated/deleted
+ij(SCANNER)> create index test_6_idx on test_6 (a);
+0 rows inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> set connection deleter;
+ij(DELETER)> autocommit off;
+ij(DELETER)> commit;
+ij(DELETER)> --------------------------------------------------------------------------------
+-- 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;
+ij(SCANNER)> get cursor scan_cursor as 
+    'select a, a2 from test_6';
+ij(SCANNER)> next scan_cursor;
+A          |A2         
+-----------------------
+1          |10         
+ij(SCANNER)> next scan_cursor;
+A          |A2         
+-----------------------
+2          |20         
+ij(SCANNER)> next scan_cursor;
+A          |A2         
+-----------------------
+3          |30         
+ij(SCANNER)> next scan_cursor;
+A          |A2         
+-----------------------
+4          |40         
+ij(SCANNER)> next scan_cursor;
+A          |A2         
+-----------------------
+5          |50         
+ij(SCANNER)> --------------------------------------------------------------------------------
+-- Delete all rows that the scanner has looked at, and should have released the
+-- lock on.
+--------------------------------------------------------------------------------
+set connection deleter;
+ij(DELETER)> delete from test_6 where a = 1;
+1 row inserted/updated/deleted
+ij(DELETER)> delete from test_6 where a = 2;
+1 row inserted/updated/deleted
+ij(DELETER)> delete from test_6 where a = 3;
+1 row inserted/updated/deleted
+ij(DELETER)> delete from test_6 where a = 4;
+1 row inserted/updated/deleted
+ij(DELETER)> --------------------------------------------------------------------------------
+-- The scan should either block on the delete or continue and not return the
+-- the deleted row.
+--------------------------------------------------------------------------------
+set connection scanner;
+ij(SCANNER)> next scan_cursor;
+No current row
+ij(SCANNER)> close scan_cursor;
+ij(SCANNER)> -- commit the delete
+set connection deleter;
+ij(DELETER)> delete from test_6 where a = 5;
+1 row inserted/updated/deleted
+ij(DELETER)> commit;
+ij(DELETER)> -- scanner should see no rows.
+set connection scanner;
+ij(SCANNER)> select a,b from test_6;
+A          |B                                                                                                                               
+--------------------------------------------------------------------------------------------------------------------------------------------
+ij(SCANNER)> --------------------------------------------------------------------------------
+-- cleanup.
+--------------------------------------------------------------------------------
+set connection deleter;
+ij(DELETER)> commit;
+ij(DELETER)> disconnect;
+ij> set connection scanner;
+ij(SCANNER)> commit;
+ij(SCANNER)> drop table test_6;
+0 rows inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> disconnect;
+ij> --------------------------------------------------------------------------------
+-- 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;
+ij(SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(SCANNER)> connect 'wombat' as deleter;
+ij(DELETER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(DELETER)> --------------------------------------------------------------------------------
+-- HEAP SCAN
+--------------------------------------------------------------------------------
+set connection scanner;
+ij(SCANNER)> autocommit off;
+ij(SCANNER)> create table test_7 (a int, a2 int, b varchar(2000), c varchar(2000)) ;
+0 rows inserted/updated/deleted
+ij(SCANNER)> insert into test_7 values (1, 10, PADSTRING('a',2000), PADSTRING('a',2000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_7 values (2, 20, PADSTRING('b',2000), PADSTRING('b',2000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_7 values (3, 30, PADSTRING('c',2000), PADSTRING('c',2000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_7 values (4, 40, PADSTRING('d',2000), PADSTRING('d',2000));
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_7 values (5, 50, PADSTRING('e',2000), PADSTRING('e',2000));
+1 row inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> set connection deleter;
+ij(DELETER)> autocommit off;
+ij(DELETER)> commit;
+ij(DELETER)> -- Set up scanner to be doing a row locked heap scan, going one row at a time. 
+set connection scanner;
+ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
+0 rows inserted/updated/deleted
+ij(SCANNER)> get cursor scan_cursor_1 as 
+    'select a, a2 from test_7';
+ij(SCANNER)> get cursor scan_cursor_2 as 
+    'select a, a2 from test_7';
+ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
+0 rows inserted/updated/deleted
+ij(SCANNER)> next scan_cursor_1;
+A          |A2         
+-----------------------
+1          |10         
+ij(SCANNER)> next scan_cursor_1;
+A          |A2         
+-----------------------
+2          |20         
+ij(SCANNER)> next scan_cursor_1;
+A          |A2         
+-----------------------
+3          |30         
+ij(SCANNER)> next scan_cursor_1;
+A          |A2         
+-----------------------
+4          |40         
+ij(SCANNER)> next scan_cursor_1;
+A          |A2         
+-----------------------
+5          |50         
+ij(SCANNER)> next scan_cursor_2;
+A          |A2         
+-----------------------
+1          |10         
+ij(SCANNER)> close scan_cursor_2;
+ij(SCANNER)> -- Get exclusive table lock on test_7.  Should fail with table cannot be locked.
+set connection deleter;
+ij(DELETER)> lock table test_7 in exclusive mode;
+ERROR X0X02: Table 'TEST_7' cannot be locked in 'EXCLUSIVE' mode.
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(DELETER)> -- release all read locks, by moving the cursor past all the rows.
+set connection scanner;
+ij(SCANNER)> next scan_cursor_1;
+No current row
+ij(SCANNER)> close scan_cursor_1;
+ij(SCANNER)> -- Get exclusive table lock on test_7.  Now that both scan closed this should
+-- work.
+set connection deleter;
+ij(DELETER)> delete from test_7;
+5 rows inserted/updated/deleted
+ij(DELETER)> commit;
+ij(DELETER)> -- scanner should see no rows.
+set connection scanner;
+ij(SCANNER)> select a,b from test_7;
+A          |B                                                                                                                               
+--------------------------------------------------------------------------------------------------------------------------------------------
+ij(SCANNER)> commit;
+ij(SCANNER)> --------------------------------------------------------------------------------
+-- cleanup.
+--------------------------------------------------------------------------------
+set connection deleter;
+ij(DELETER)> commit;
+ij(DELETER)> disconnect;
+ij> set connection scanner;
+ij(SCANNER)> commit;
+ij(SCANNER)> drop table test_7;
+0 rows inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> disconnect;
+ij> --------------------------------------------------------------------------------
+-- 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;
+ij(CLIENT_1)> create table test_8 (a int, a2 int, b varchar(2000), c char(10)) 
+    ;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> create index test_8_idx on test_8 (a);
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (1, 10, PADSTRING('a',2000), 'test 8.1');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (2, 20, PADSTRING('b',2000), 'test 8.1');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (3, 30, PADSTRING('c',2000), 'test 8.1');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (4, 40, PADSTRING('d',2000), 'test 8.1');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (5, 50, PADSTRING('e',2000), 'test 8.1');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> --------------------------------------------------------------------------------
+-- 8.1 test - simply delete rows from table, heap post commit will run and 
+--            reclaim all pages.
+--------------------------------------------------------------------------------
+set connection client_1;
+ij(CLIENT_1)> delete from test_8;
+5 rows inserted/updated/deleted
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> select a from test_8;
+A          
+-----------
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> --------------------------------------------------------------------------------
+-- 8.2 setup 
+--------------------------------------------------------------------------------
+drop table test_8;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> create table test_8 (a int, a2 int, b varchar(1000), c char(10))
+    ;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> create index test_8_idx on test_8 (a);
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (1, 10, PADSTRING('a',1000), 'test 8.2');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (2, 20, PADSTRING('b',1000), 'test 8.2');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (3, 30, PADSTRING('c',1000), 'test 8.2');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (4, 40, PADSTRING('d',1000), 'test 8.2');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (5, 50, PADSTRING('e',1000), 'test 8.2');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> --------------------------------------------------------------------------------
+-- 8.2 test - client 1 holds row lock which will prevent client 2 post commit.
+--------------------------------------------------------------------------------
+set connection client_1;
+ij(CLIENT_1)> insert into test_8 values (6, 60, PADSTRING('a',1000), 'test 8.2');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> set connection client_2;
+ij(CLIENT_2)> delete from test_8 where a < 5;
+4 rows inserted/updated/deleted
+ij(CLIENT_2)> commit;
+ij(CLIENT_2)> set connection client_1;
+ij(CLIENT_1)> select a from test_8;
+A          
+-----------
+5          
+6          
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> set connection client_2;
+ij(CLIENT_2)> select a from test_8;
+A          
+-----------
+5          
+6          
+ij(CLIENT_2)> commit;
+ij(CLIENT_2)> --------------------------------------------------------------------------------
+-- 8.3 setup 
+--------------------------------------------------------------------------------
+drop table test_8;
+0 rows inserted/updated/deleted
+ij(CLIENT_2)> create table test_8 (a int, a2 int, b varchar(1500), c char(10));
+0 rows inserted/updated/deleted
+ij(CLIENT_2)> create index test_8_idx on test_8 (a, b)
+    ;
+0 rows inserted/updated/deleted
+ij(CLIENT_2)> insert into test_8 values (1, 10, PADSTRING('a',1500), 'test 8.3');
+1 row inserted/updated/deleted
+ij(CLIENT_2)> insert into test_8 values (2, 20, PADSTRING('b',1500), 'test 8.3');
+1 row inserted/updated/deleted
+ij(CLIENT_2)> insert into test_8 values (3, 30, PADSTRING('c',1500), 'test 8.3');
+1 row inserted/updated/deleted
+ij(CLIENT_2)> insert into test_8 values (4, 40, PADSTRING('d',1500), 'test 8.3');
+1 row inserted/updated/deleted
+ij(CLIENT_2)> insert into test_8 values (5, 50, PADSTRING('e',1500), 'test 8.3');
+1 row inserted/updated/deleted
+ij(CLIENT_2)> commit;
+ij(CLIENT_2)> --------------------------------------------------------------------------------
+-- 8.3 test - simply delete rows from index table, btree post commit will run
+--            and reclaim all pages.
+--------------------------------------------------------------------------------
+set connection client_1;
+ij(CLIENT_1)> delete from test_8;
+5 rows inserted/updated/deleted
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> select a from test_8;
+A          
+-----------
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> --------------------------------------------------------------------------------
+-- 8.4 setup 
+--------------------------------------------------------------------------------
+drop table test_8;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> create table test_8 (a int, a2 int, b varchar(1500), c char(10)) ;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> create index test_8_idx1 on test_8 (a);
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> create index test_8_idx2 on test_8 (a, b)
+    ;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (1, 10, PADSTRING('a',1500), 'test 8.4');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (2, 20, PADSTRING('b',1500), 'test 8.4');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (3, 30, PADSTRING('c',1500), 'test 8.4');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (4, 40, PADSTRING('d',1500), 'test 8.4');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> insert into test_8 values (5, 50, PADSTRING('e',1500), 'test 8.4');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> --------------------------------------------------------------------------------
+-- 8.4 test - client 1 holds row lock which will prevent client 2 post commit.
+--------------------------------------------------------------------------------
+set connection client_1;
+ij(CLIENT_1)> insert into test_8 values (6, 60, PADSTRING('a',1500), 'test 8.4');
+1 row inserted/updated/deleted
+ij(CLIENT_1)> set connection client_2;
+ij(CLIENT_2)> delete from test_8 where a < 5;
+4 rows inserted/updated/deleted
+ij(CLIENT_2)> commit;
+ij(CLIENT_2)> set connection client_1;
+ij(CLIENT_1)> select a from test_8;
+A          
+-----------
+5          
+6          
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> set connection client_2;
+ij(CLIENT_2)> select a from test_8;
+A          
+-----------
+5          
+6          
+ij(CLIENT_2)> commit;
+ij(CLIENT_2)> --------------------------------------------------------------------------------
+-- cleanup.
+--------------------------------------------------------------------------------
+set connection client_1;
+ij(CLIENT_1)> drop table test_8;
+0 rows inserted/updated/deleted
+ij(CLIENT_1)> commit;
+ij(CLIENT_1)> disconnect;
+ij> set connection client_2;
+ij(CLIENT_2)> commit;
+ij(CLIENT_2)> disconnect;
+ij> --------------------------------------------------------------------------------
+-- Test 9: Make sure scan positioning in the beginning of a unique scan
+--         properly gets the scan lock to block with splits.
+--
+--------------------------------------------------------------------------------
+--------------------------------------------------------------------------------
+-- 9.1 setup 
+--------------------------------------------------------------------------------
+connect 'wombat' as scanner;
+ij(SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(SCANNER)> autocommit off;
+ij(SCANNER)> commit;
+ij(SCANNER)> connect 'wombat' as splitter;
+ij(SPLITTER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(SPLITTER)> autocommit off;
+ij(SPLITTER)> commit;
+ij(SPLITTER)> set connection scanner;
+ij(SCANNER)> create table test_9 (a int, a2 int, b varchar(1000), c char(10)) 
+    ;
+0 rows inserted/updated/deleted
+ij(SCANNER)> insert into test_9 values (5, 50, PADSTRING('e',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_9 values (4, 40, PADSTRING('d',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_9 values (3, 30, PADSTRING('c',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_9 values (2, 20, PADSTRING('b',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(SCANNER)> insert into test_9 values (1, 10, PADSTRING('a',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(SCANNER)> create unique index test_9_idx on test_9 (b) ;
+0 rows inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> --------------------------------------------------------------------------------
+-- 9.1 test - open a cursor for update on table, and make sure splitter waits
+--            on the scan position.
+--------------------------------------------------------------------------------
+set connection scanner;
+ij(SCANNER)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1');
+0 rows inserted/updated/deleted
+ij(SCANNER)> get cursor scan_cursor as
+    'select b from test_9 where b >= ''a'' ';
+ij(SCANNER)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16');
+0 rows inserted/updated/deleted
+ij(SCANNER)> next scan_cursor;
+B                                                                                                                               
+--------------------------------------------------------------------------------------------------------------------------------
+b                                                                                                                              &
+ij(SCANNER)> next scan_cursor;
+B                                                                                                                               
+--------------------------------------------------------------------------------------------------------------------------------
+c                                                                                                                              &
+ij(SCANNER)> -- the following will get a couple of rows and then block on the split.
+set connection splitter;
+ij(SPLITTER)> insert into test_9 values (0, 10, PADSTRING('aa',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(SPLITTER)> commit;
+ij(SPLITTER)> insert into test_9 values (0, 10, PADSTRING('ab',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(SPLITTER)> commit;
+ij(SPLITTER)> -- insert ahead in the cursor to make sure we pick it up later.
+insert into test_9 values (0, 10, PADSTRING('dd',1000), 'test 9.1');
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(SPLITTER)> commit;
+ij(SPLITTER)> set connection scanner;
+ij(SCANNER)> next scan_cursor;
+B                                                                                                                               
+--------------------------------------------------------------------------------------------------------------------------------
+d                                                                                                                              &
+ij(SCANNER)> next scan_cursor;
+B                                                                                                                               
+--------------------------------------------------------------------------------------------------------------------------------
+e                                                                                                                              &
+ij(SCANNER)> next scan_cursor;
+No current row
+ij(SCANNER)> next scan_cursor;
+No current row
+ij(SCANNER)> commit;
+ij(SCANNER)> --------------------------------------------------------------------------------
+-- cleanup.
+--------------------------------------------------------------------------------
+set connection scanner;
+ij(SCANNER)> drop table test_9;
+0 rows inserted/updated/deleted
+ij(SCANNER)> commit;
+ij(SCANNER)> disconnect;
+ij> set connection splitter;
+ij(SPLITTER)> commit;
+ij(SPLITTER)> disconnect;
+ij> --------------------------------------------------------------------------------
+-- Test 10: Make sure a ddl does not block the lock table vti.
+--------------------------------------------------------------------------------
+--------------------------------------------------------------------------------
+-- 10 setup 
+--------------------------------------------------------------------------------
+connect 'wombat' as ddl;
+ij(DDL)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(DDL)> autocommit off;
+ij(DDL)> commit;
+ij(DDL)> connect 'wombat' as locktable;
+ij(LOCKTABLE)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(LOCKTABLE)> autocommit off;
+ij(LOCKTABLE)> commit;
+ij(LOCKTABLE)> set connection ddl;
+ij(DDL)> run resource 'LockTableQuery.subsql';
+ij(DDL)> create view lock_table as
+select 
+    cast(username as char(8)) as username,
+    cast(t.type as char(8)) as trantype,
+    cast(l.type as char(8)) as type,
+    cast(lockcount as char(3)) as cnt,
+    mode,
+    cast(tablename as char(12)) as tabname,
+    cast(lockname as char(10)) as lockname,
+    state,
+    status
+from 
+    new org.apache.derby.diag.LockTable() l  right outer join new org.apache.derby.diag.TransactionTable() t
+on l.xid = t.xid where l.tableType <> 'S' and t.type='UserTransaction';
+0 rows inserted/updated/deleted
+ij(DDL)> --on l.xid = t.xid where l.tableType <> 'S' or l.tableType is null
+-- order by
+--     tabname, type desc, mode, cnt, lockname
+-- lock table with system catalog locks included.
+create view full_lock_table as
+select 
+    cast(username as char(8)) as username,
+    cast(t.type as char(8)) as trantype,
+    cast(l.type as char(8)) as type,
+    cast(lockcount as char(3)) as cnt,
+    mode,
+    cast(tablename as char(12)) as tabname,
+    cast(lockname as char(10)) as lockname,
+    state,
+    status
+from 
+    new org.apache.derby.diag.LockTable() l right outer join new org.apache.derby.diag.TransactionTable() t
+on l.xid = t.xid where l.tableType <> 'S' ;
+0 rows inserted/updated/deleted
+ij(DDL)> -- lock table with no join.
+create view lock_table2 as
+select 
+    cast(l.xid as char(8)) as xid,
+    cast(l.type as char(8)) as type,
+    cast(lockcount as char(3)) as cnt,
+    mode,
+    cast(tablename as char(12)) as tabname,
+    cast(lockname as char(10)) as lockname,
+    state
+from 
+    new org.apache.derby.diag.LockTable() l  
+where l.tableType <> 'S' ;
+0 rows inserted/updated/deleted
+ij(DDL)> -- transaction table with no join.
+create view tran_table as
+select 
+    *
+from 
+    new org.apache.derby.diag.TransactionTable() t ;
+0 rows inserted/updated/deleted
+ij(DDL)> commit;
+ij(DDL)> --------------------------------------------------------------------------------
+-- 10 test - do ddl in one connection and look at lock table in another 
+--           connection.
+--------------------------------------------------------------------------------
+set connection ddl;
+ij(DDL)> create table test_10 (a int, a2 int, b varchar(1000), c char(10)) 
+    ;
+0 rows inserted/updated/deleted
+ij(DDL)> insert into test_10 values (4, 40, PADSTRING('d',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(DDL)> insert into test_10 values (3, 30, PADSTRING('c',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(DDL)> insert into test_10 values (2, 20, PADSTRING('b',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(DDL)> insert into test_10 values (1, 10, PADSTRING('a',1000), 'test 9.1');
+1 row inserted/updated/deleted
+ij(DDL)> set connection locktable;
+ij(LOCKTABLE)> -- this should not block on the other thread.
+select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |4   |IX  |TEST_10     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|TABLE   |1   |X   |TEST_10     |Tablelock |GRANT|ACTIVE  
+ij(LOCKTABLE)> commit;
+ij(LOCKTABLE)> --------------------------------------------------------------------------------
+-- cleanup.
+--------------------------------------------------------------------------------
+set connection ddl;
+ij(DDL)> drop table test_10;
+0 rows inserted/updated/deleted
+ij(DDL)> commit;
+ij(DDL)> disconnect;
+ij> set connection locktable;
+ij(LOCKTABLE)> commit;
+ij(LOCKTABLE)> disconnect;
+ij> --------------------------------------------------------------------------------
+-- Test 11: test update locks
+--------------------------------------------------------------------------------
+connect 'wombat' as t11scanner;
+ij(T11SCANNER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(T11SCANNER)> connect 'wombat' as t11updater;
+ij(T11UPDATER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(T11UPDATER)> connect 'wombat' as t11writer;
+ij(T11WRITER)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
+NoHoldForConnection;
+ij(T11WRITER)> -- set up
+set connection t11updater;
+ij(T11UPDATER)> autocommit off;
+ij(T11UPDATER)> create table test_11 (a int, b int);
+0 rows inserted/updated/deleted
+ij(T11UPDATER)> insert into test_11 values (1,1);
+1 row inserted/updated/deleted
+ij(T11UPDATER)> insert into test_11 values (2,2);
+1 row inserted/updated/deleted
+ij(T11UPDATER)> insert into test_11 values (8,8);
+1 row inserted/updated/deleted
+ij(T11UPDATER)> create index test11_idx on test_11 (a);
+0 rows inserted/updated/deleted
+ij(T11UPDATER)> commit;
+ij(T11UPDATER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+ij(T11UPDATER)> --
+--==================================================
+-- 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';
+ij(T11UPDATER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+ij(T11UPDATER)> next update_cursor;
+B          
+-----------
+1          
+ij(T11UPDATER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
+ij(T11UPDATER)> set connection t11scanner;
+ij(T11SCANNER)> autocommit off;
+ij(T11SCANNER)> --
+--------------------------------------------------
+-- try to scan the table, readers are compatible with update lock.
+--------------------------------------------------
+select * from test_11;
+A          |B          
+-----------------------
+1          |1          
+2          |2          
+8          |8          
+ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
+ij(T11SCANNER)> --
+--------------------------------------------------
+-- try to update the table, should timeout
+--------------------------------------------------
+update test_11 set b=99 where a = 1;
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(T11SCANNER)> --
+--------------------------------------------------
+-- try to update the table, should go through
+--------------------------------------------------
+update test_11 set b=99 where a = 8;
+1 row inserted/updated/deleted
+ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|TABLE   |2   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |3   |X   |TEST_11     |(1,9)     |GRANT|ACTIVE  
+ij(T11SCANNER)> commit;
+ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
+ij(T11SCANNER)> --
+--------------------------------------------------
+-- try to get an update lock
+--------------------------------------------------
+get cursor update_cursor2 as
+    'select b from test_11 where a=1 for update of b';
+ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
+ij(T11SCANNER)> --
+--------------------------------------------------
+-- should timeout (other transaction has a shared lock on this row)
+--------------------------------------------------
+next update_cursor2;
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
+ij(T11SCANNER)> --
+--------------------------------------------------
+-- 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';
+ij(T11SCANNER)> select type, cnt, mode, tabname, lockname, state from lock_table2 order by tabname, type desc, mode, cnt, lockname;
+TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE
+------------------------------------------------
+TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT
+TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT
+ROW     |1   |S   |TEST_11     |(1,1)     |GRANT
+ROW     |1   |U   |TEST_11     |(1,7)     |GRANT
+ij(T11SCANNER)> next update_cursor3;
+B          
+-----------
+99         
+ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,9)     |GRANT|ACTIVE  
+ij(T11SCANNER)> commit;
+ij(T11SCANNER)> close update_cursor2;
+ij(T11SCANNER)> close update_cursor3;
+ij(T11SCANNER)> set connection t11updater;
+ij(T11UPDATER)> commit;
+ij(T11UPDATER)> close update_cursor;
+ij(T11UPDATER)> set connection t11scanner;
+ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+ij(T11SCANNER)> --
+--==================================================
+-- t11scanner gets a read lock
+--==================================================
+select b from test_11 where a=1;
+B          
+-----------
+1          
+ij(T11SCANNER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+ij(T11SCANNER)> --
+--------------------------------------------------
+-- should succeed (can get an update lock if there is already a shared lock)
+--------------------------------------------------
+set connection t11updater;
+ij(T11UPDATER)> get cursor update_cursor as
+    'select b from test_11 where a=1 for update of b';
+ij(T11UPDATER)> next update_cursor;
+B          
+-----------
+1          
+ij(T11UPDATER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |S   |TEST_11     |(1,1)     |GRANT|ACTIVE  
+APP     |UserTran|ROW     |1   |U   |TEST_11     |(1,7)     |GRANT|ACTIVE  
+ij(T11UPDATER)> commit;
+ij(T11UPDATER)> close update_cursor;
+ij(T11UPDATER)> set connection t11scanner;
+ij(T11SCANNER)> commit;
+ij(T11SCANNER)> --
+--==================================================
+-- t11writer gets a write lock
+--==================================================
+set connection t11writer;
+ij(T11WRITER)> autocommit off;
+ij(T11WRITER)> update test_11 set b=77 where a=2;
+1 row inserted/updated/deleted
+ij(T11WRITER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |2   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|ROW     |3   |X   |TEST_11     |(1,8)     |GRANT|ACTIVE  
+ij(T11WRITER)> set connection t11updater;
+ij(T11UPDATER)> get cursor update_cursor as
+    'select b from test_11 where a=2 for update of b';
+ij(T11UPDATER)> select * from lock_table order by tabname, type desc, mode, cnt, lockname;
+USERNAME|TRANTYPE|TYPE    |CNT |MODE|TABNAME     |LOCKNAME  |STATE|STATUS  
+---------------------------------------------------------------------------
+APP     |UserTran|TABLE   |1   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|TABLE   |2   |IX  |TEST_11     |Tablelock |GRANT|ACTIVE  
+APP     |UserTran|ROW     |3   |X   |TEST_11     |(1,8)     |GRANT|ACTIVE  
+ij(T11UPDATER)> --
+----------------------------------------------------
+-- should timeout
+----------------------------------------------------
+next update_cursor;
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(T11UPDATER)> --------------------------------------------------------------------------------
+-- cleanup.
+--------------------------------------------------------------------------------
+set connection t11updater;
+ij(T11UPDATER)> close update_cursor;
+ij(T11UPDATER)> drop table test_11;
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(T11UPDATER)> commit;
+ij(T11UPDATER)> disconnect;
+ij> set connection t11scanner;
+ij(T11SCANNER)> disconnect;
+ij> set connection t11writer;
+ij(T11WRITER)> disconnect;
+ij> exit;

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall?view=diff&rev=109565&p1=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall&r1=109564&p2=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall&r2=109565
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall	(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/derbylang.runall	Thu Dec  2 13:59:42 2004
@@ -69,7 +69,6 @@
 lang/isolationLevels.sql
 lang/joinDeadlock.sql
 lang/joins.sql
-lang/langUnitTests.sql
 lang/lockTable.sql
 lang/logStream.java
 lang/logop.sql

Modified: incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storemore.runall
Url: http://svn.apache.org/viewcvs/incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storemore.runall?view=diff&rev=109565&p1=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storemore.runall&r1=109564&p2=incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storemore.runall&r2=109565
==============================================================================
--- incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storemore.runall	(original)
+++ incubator/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/suites/storemore.runall	Thu Dec  2 13:59:42 2004
@@ -14,7 +14,6 @@
 store/SpaceTable.sql
 store/testsqldecimal.sql
 store/heapscan.sql
-store/readlocks.sql
 store/removeStubs.sql
 store/lockTableVti.sql
 store/rollForwardRecovery.sql

Mime
View raw message