| 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. |
| -- |
| -- Very basic single user testing of row locking, verify that the right locks |
| -- are obtained for simple operations. This test only looks at table and |
| -- row logical locks, it does not verify physical latches or lock ordering. |
| -- The basic methodology is: |
| -- start transaction |
| -- simple operation |
| -- print lock table which should match the master |
| -- end transation |
| -- |
| 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)> run resource '/org/apache/derbyTesting/functionTests/tests/store/LockTableQuery.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 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(CONNECTION1)> --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(CONNECTION1)> -- 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(CONNECTION1)> -- transaction table with no join. |
| create view tran_table as |
| select |
| * |
| from |
| syscs_diag.transaction_table; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> autocommit off; |
| ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize', '4096'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> create table a (a int, b int, c varchar(1900)) ; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> set isolation read committed; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test select from empty heap table |
| -------------------------------------------------------------------------------- |
| select a, b from a; |
| A |B |
| ----------------------- |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test select from one row heap table |
| -------------------------------------------------------------------------------- |
| insert into a values (1, -1, PADSTRING('one',1900)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |X |A |(1,7) |GRANT|ACTIVE |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> select a, b from a; |
| A |B |
| ----------------------- |
| 1 |-1 |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test select from two row heap table - this will release one row lock as it |
| -- moves to the next one. |
| -------------------------------------------------------------------------------- |
| insert into a values (2, -2, PADSTRING('two',1900)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> select a,b from a; |
| A |B |
| ----------------------- |
| 1 |-1 |
| 2 |-2 |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test select from three row heap table (multiple pages) - this will release |
| -- one row lock as it moves to the next one. |
| -------------------------------------------------------------------------------- |
| insert into a values (3, -3, PADSTRING('two',1900)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into a values (4, -4, PADSTRING('two',1900)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into a values (5, -5, PADSTRING('two',1900)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| APP |UserTransaction|TABLE |3 |IX |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |X |A |(2,6) |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |X |A |(2,7) |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |X |A |(3,6) |GRANT|ACTIVE |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> delete from a where a.a = 3; |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |X |A |(2,6) |GRANT|ACTIVE |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> select a,b from a; |
| A |B |
| ----------------------- |
| 1 |-1 |
| 2 |-2 |
| 4 |-4 |
| 5 |-5 |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test full read cursor scan over all the rows in the heap, no group fetch. |
| -------------------------------------------------------------------------------- |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the query 'select a, b from a' to be recompiled |
| create index ix1 on a(a); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> drop index ix1; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> get cursor scan_cursor as |
| 'select a, b from a'; |
| ij(CONNECTION1)> call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 1 |-1 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |S |A |(1,7) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 2 |-2 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |S |A |(1,8) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 4 |-4 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |S |A |(2,7) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 5 |-5 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |S |A |(3,6) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| No current row |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> close scan_cursor; |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','16'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test full cursor scan over all the rows in the heap, with 2 row group fetch. |
| -------------------------------------------------------------------------------- |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the query 'select a, b from a' to be recompiled |
| create index ix1 on a(a); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> drop index ix1; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> get cursor scan_cursor as |
| 'select a, b from a'; |
| ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','16'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 1 |-1 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 2 |-2 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 4 |-4 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 5 |-5 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| No current row |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> close scan_cursor; |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test full cursor scan over all the rows in the heap, with default group fetch |
| -------------------------------------------------------------------------------- |
| get cursor scan_cursor as |
| 'select a, b from a'; |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 1 |-1 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 2 |-2 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 4 |-4 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 5 |-5 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| No current row |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> close scan_cursor; |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test full cursor for update scan over all the rows in the heap, |
| -- with default group fetch. Group fetch should be disabled. |
| -------------------------------------------------------------------------------- |
| get cursor scan_cursor as |
| 'select a, b from a for update'; |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 1 |-1 |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |U |A |(1,7) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 2 |-2 |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |U |A |(1,8) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 4 |-4 |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |U |A |(2,7) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 5 |-5 |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |U |A |(3,6) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| No current row |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> close scan_cursor; |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test full read cursor scan on a join over all the rows in the btree, |
| -- 2 row group fetch. |
| -------------------------------------------------------------------------------- |
| |
| drop table a; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> create table a (a int, b int, c varchar(1900), d int, e varchar(2000)) ; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> create index a_idx on a (a, b, c) ; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> create table b (a int, b int, c varchar(1900)) ; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> insert into b values (1, -1, PADSTRING('one',1900)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into b values (2, -2, PADSTRING('two',1900)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into b values (3, -3, PADSTRING('three',1900)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into b values (4, -4, PADSTRING('four',1900)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into b values (5, -5, PADSTRING('five',1900)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test select from empty index |
| -------------------------------------------------------------------------------- |
| select a, b from a; |
| A |B |
| ----------------------- |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test select from one row index'd table |
| -------------------------------------------------------------------------------- |
| insert into a values (5, -5, PADSTRING('five',1900), 5, PADSTRING('negative five',2000)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |X |A |(1,7) |GRANT|ACTIVE |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> select a, b from a; |
| A |B |
| ----------------------- |
| 5 |-5 |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test select from two row indexed heap table - this will release one row |
| -- lock as it moves to the next one. |
| -------------------------------------------------------------------------------- |
| insert into a values (4, -4, PADSTRING('four',1900), 4, PADSTRING('negative four',2000)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> select a,b from a; |
| A |B |
| ----------------------- |
| 4 |-4 |
| 5 |-5 |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test select from three row indexed heap table (multiple pages) - this will |
| -- release one row lock as it moves to the next one. |
| -------------------------------------------------------------------------------- |
| insert into a values (3, -3, PADSTRING('three',1900), 3, PADSTRING('negative three',2000)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into a values (2, -2, PADSTRING('two',1900), 2, PADSTRING('negative two',2000)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> insert into a values (1, -1, PADSTRING('one',1900), 1, PADSTRING('negtive one',2000)); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| APP |UserTransaction|TABLE |6 |IX |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |X |A |(3,6) |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |X |A |(4,6) |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |X |A |(5,6) |GRANT|ACTIVE |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> delete from a where a.a = 3; |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> 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 |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |2 |X |A |(3,6) |GRANT|ACTIVE |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> select a,b from a; |
| A |B |
| ----------------------- |
| 1 |-1 |
| 2 |-2 |
| 4 |-4 |
| 5 |-5 |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test full read cursor scan over all the rows in the indexed heap, |
| -- no group fetch. This should be a covered index scan (make sure rows come |
| -- back in order sorted by index). |
| -------------------------------------------------------------------------------- |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the query 'select a, b from a' to be recompiled |
| create index ix1 on a(a); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> drop index ix1; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> get cursor scan_cursor as |
| 'select a, b from a'; |
| ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','16'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 1 |-1 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |S |A |(5,6) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 2 |-2 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |S |A |(4,6) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 4 |-4 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |S |A |(2,6) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 5 |-5 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |S |A |(1,7) |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| No current row |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> close scan_cursor; |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test full cursor scan over all the rows in the index , 2 row group fetch. |
| -------------------------------------------------------------------------------- |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the query 'select a, b from a' to be recompiled |
| create index ix1 on a(a); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> drop index ix1; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> get cursor scan_cursor as |
| 'select a, b from a'; |
| ij(CONNECTION1)> CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','16'); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 1 |-1 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 2 |-2 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 4 |-4 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 5 |-5 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| No current row |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> close scan_cursor; |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test full cursor scan over all the rows in the index, with default group |
| -- fetch |
| -------------------------------------------------------------------------------- |
| |
| -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the query 'select a, b from a' to be recompiled |
| create index ix1 on a(a); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> drop index ix1; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> get cursor scan_cursor as |
| 'select a, b from a'; |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 1 |-1 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 2 |-2 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 4 |-4 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| A |B |
| ----------------------- |
| 5 |-5 |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> next scan_cursor; |
| No current row |
| ij(CONNECTION1)> 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 |IS |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> close scan_cursor; |
| ij(CONNECTION1)> select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| USERNAME|TRANTYPE |TYPE |CNT |MODE|TABNAME |LOCKNAME |STATE|STATUS |
| ---------------------------------------------------------------------------------- |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test getting index lock on a drop index - track 1634 |
| -------------------------------------------------------------------------------- |
| |
| drop table a; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> create table a (a int); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> create index a2 on a (a); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> insert into a values (1); |
| 1 row inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> drop index a2; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> 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 |X |*** TRANSIEN|Tablelock |GRANT|ACTIVE |
| WARNING 01004: Data truncation |
| APP |UserTransaction|TABLE |3 |X |A |Tablelock |GRANT|ACTIVE |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> drop table a; |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> |
