Return-Path: Mailing-List: contact derby-commits-help@db.apache.org; run by ezmlm Delivered-To: mailing list derby-commits@db.apache.org Received: (qmail 36338 invoked by uid 500); 2 Dec 2004 21:59:46 -0000 Delivered-To: apmail-incubator-derby-cvs@incubator.apache.org Received: (qmail 36335 invoked by uid 99); 2 Dec 2004 21:59:46 -0000 X-ASF-Spam-Status: No, hits=-10.0 required=10.0 tests=ALL_TRUSTED,NO_REAL_NAME X-Spam-Check-By: apache.org Received: from minotaur.apache.org (HELO minotaur.apache.org) (209.237.227.194) by apache.org (qpsmtpd/0.28) with SMTP; Thu, 02 Dec 2004 13:59:44 -0800 Received: (qmail 88136 invoked by uid 65534); 2 Dec 2004 21:59:43 -0000 Date: 2 Dec 2004 21:59:43 -0000 Message-ID: <20041202215943.88131.qmail@minotaur.apache.org> From: kmarsden@apache.org To: derby-cvs@incubator.apache.org Subject: svn commit: r109565 - in incubator/derby/code/trunk/java/testing: . org/apache/derbyTesting/functionTests/master org/apache/derbyTesting/functionTests/suites MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 X-Virus-Checked: Checked 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 @@ + 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