| ij(CONNECTION1)> -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| -------------------------------------------------------------------------------- |
| -- Test multi user lock interaction under isolation level 2. default isolation |
| -- level has been set as a property to serializable. |
| -------------------------------------------------------------------------------- |
| run resource '/org/apache/derbyTesting/functionTests/tests/store/createTestProcedures.subsql'; |
| ij(CONNECTION1)> -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| 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(CONNECTION1)> 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(CONNECTION1)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> autocommit off; |
| ij(CONNECTION1)> -- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection. |
| NoHoldForConnection; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- 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)> -------------------------------------------------------------------------------- |
| -- Before DERBY-2991 the attempt to split the root would time out because the |
| -- scan had locked the first page. |
| -------------------------------------------------------------------------------- |
| set connection rootgrower; |
| ij(ROOTGROWER)> autocommit off; |
| ij(ROOTGROWER)> insert into a values (PADSTRING('d',1200), PADSTRING('d',1000)); |
| 1 row inserted/updated/deleted |
| ij(ROOTGROWER)> rollback; |
| 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). |
| -- (Scan locks are no longer used after DERBY-2991.) |
| -------------------------------------------------------------------------------- |
| 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 '/org/apache/derbyTesting/functionTests/tests/store/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 '/org/apache/derbyTesting/functionTests/tests/store/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. |
| -- (Scan locks are no longer used after DERBY-2991.) |
| -- |
| -------------------------------------------------------------------------------- |
| |
| -------------------------------------------------------------------------------- |
| -- 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. |
| -- This would time out before DERBY-2991. |
| insert into test_9 values (0, 10, PADSTRING('dd',1000), 'test 9.1'); |
| 1 row inserted/updated/deleted |
| ij(SPLITTER)> rollback; |
| 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 '/org/apache/derbyTesting/functionTests/tests/store/LockTableQuery.subsql'; |
| ij(DDL)> -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| create view lock_table as |
| select |
| cast(username as char(8)) as username, |
| cast(t.type as char(15)) 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 |
| syscs_diag.lock_table l right outer join syscs_diag.transaction_table 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 |
| syscs_diag.lock_table l right outer join syscs_diag.transaction_table t |
| on l.xid = t.xid where l.tableType <> 'S' ; |
| 0 rows inserted/updated/deleted |
| ij(DDL)> -- lock table with no join. |
| -- DERBY-6183, removing CAST's to avoid background locks causing |
| -- data trunction warnings. |
| |
| create view lock_table2 as |
| select |
| xid, |
| type, |
| lockcount as cnt, |
| mode, |
| tablename as tabname, |
| lockname, |
| state |
| from |
| syscs_diag.lock_table |
| where tableType <> 'S' ; |
| 0 rows inserted/updated/deleted |
| ij(DDL)> -- transaction table with no join. |
| create view tran_table as |
| select |
| * |
| from |
| syscs_diag.transaction_table; |
| 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 |UserTransaction|TABLE |4 |IX |TEST_10 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|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 |UserTransaction|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 |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|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 |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|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 |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|TABLE |2 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |U |TEST_11 |(1,7) |GRANT|ACTIVE |
| APP |UserTransaction|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 |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|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 |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|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 |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|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 |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 |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |U |TEST_11 |(1,7) |GRANT|ACTIVE |
| APP |UserTransaction|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 |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|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 |UserTransaction|TABLE |2 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|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 |UserTransaction|TABLE |1 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|TABLE |2 |IX |TEST_11 |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|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; |
