| -- |
| -- 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 read locks on cursors on heap tables. |
| -- This ".subsql" test is |
| -- meant to be run from another test such that it gets run under multiple |
| -- isolation levels. This is important as they behave |
| -- differently, depending on isolation levels. |
| -- |
| -- assume's caller has already done: run 'LockTableQuery.subsql'; to get |
| -- easy access to the lock VTI. |
| |
| -- TEST 0: heap cursor scan, group fetch = 1 |
| -- TEST 1: heap cursor scan, group fetch = 2 |
| -- TEST 2: heap cursor scan, group fetch = 1, stop scan in middle of set |
| -- TEST 3: heap cursor scan, group fetch = 4, stop scan in middle of set |
| -- TEST 4: heap cursor scan, group fetch = 1 (scan table with some deleted rows) |
| -- TEST 5: heap cursor scan, group fetch = 2 (scan with committed deleted rows) |
| -- TEST 6: heap cursor scan, group fetch = 1 (scan with "<" qualifier) |
| -- TEST 7: heap cursor scan, group fetch = 2 (scan with "<" qualifier) |
| -- TEST 8: heap cursor scan, group fetch = 1 (scan with equals qualifier) |
| -- TEST 9: heap cursor scan, group fetch = 2 (scan with equals qualifier) |
| -- TEST 10: heap cursor scan, group fetch = 1 (equals qualifier, no rows return) |
| |
| autocommit off; |
| |
| -------------------------------------------------------------------------------- |
| -- Assumes that calling routine has set up the following simple dataset, |
| -- a heap, no indexes with following initial values: |
| -- create table (a int, b int, c somesortofchar); |
| -- 1, 10, 'one' |
| -- 2, 20, 'two' |
| -- 3, 30, 'three' |
| -- 4, 40, 'four' |
| -- 5, 50, 'five' |
| -- 6, 60, 'six' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| select * from a; |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 0: heap cursor scan, group fetch = 1 |
| -- Test full cursor scan which does no updates. |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row. |
| -- READ COMMITTED - TABLE IS, will hold single S lock on current row. |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -------------------------------------------------------------------------------- |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1'); |
| get cursor scan_cursor as |
| 'select a, b, c from a'; |
| call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 1: heap cursor scan, group fetch = 2 |
| -- Test full cursor scan which does no updates. |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row. |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -------------------------------------------------------------------------------- |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2'); |
| |
| -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the query 'select a, b, c from a' to be recompiled |
| create index ix1 on a(a); |
| drop index ix1; |
| commit; |
| |
| get cursor scan_cursor as |
| 'select a, b, c from a'; |
| call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 2: heap cursor scan, group fetch = 1, stop scan in middle of set |
| -- Test full cursor scan which does no updates. |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row. |
| -- READ COMMITTED - TABLE IS, will hold single S lock on current row. |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -------------------------------------------------------------------------------- |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1'); |
| |
| -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the query 'select a, b, c from a' to be recompiled |
| create index ix1 on a(a); |
| drop index ix1; |
| commit; |
| |
| get cursor scan_cursor as |
| 'select a, b, c from a'; |
| call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 3: heap cursor scan, group fetch = 4, stop scan in middle of set |
| -- Test full cursor scan which does no updates. |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row. |
| -- READ COMMITTED - TABLE IS, will hold single S lock on current row. |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -------------------------------------------------------------------------------- |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','4'); |
| |
| -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the query 'select a, b, c from a' to be recompiled |
| create index ix1 on a(a); |
| drop index ix1; |
| commit; |
| |
| get cursor scan_cursor as |
| 'select a, b, c from a'; |
| call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 4: heap cursor scan, group fetch = 1 (scan table with some deleted rows) |
| -- Test full cursor scan on a data set with some deleted rows (the "even" ones). |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -- |
| -- After the delete the table should look like: |
| -- 1, 10, 'one' |
| -- 3, 30, 'three' |
| -- 5, 50, 'five' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| delete from a where a = 2 or a = 4 or a = 6; |
| commit; |
| |
| -- The above delete and commit can allow post commit processing to run before |
| -- the subsequent select is run, most likely if this run is on a table where |
| -- there is one row per page. To get reproducible results from the subsequent |
| -- lock calls, wait for post commit to finish before running the select. |
| -- Without this wait "extra" locks sometimes might show up - these are locks |
| -- on the committed deleted rows which have not been cleaned yet depending |
| -- on timing. |
| CALL WAIT_FOR_POST_COMMIT(); |
| |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1'); |
| |
| -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the query 'select a, b, c from a' to be recompiled |
| create index ix1 on a(a); |
| drop index ix1; |
| commit; |
| |
| get cursor scan_cursor as |
| 'select a, b, c from a'; |
| call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 5: heap cursor scan, group fetch = 2 (scan with committed deleted rows) |
| -- Test full cursor scan on a data set with some deleted rows (the "even" ones). |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -- |
| -- At this point the table should look like: |
| -- 1, 10, 'one' |
| -- 3, 30, 'three' |
| -- 5, 50, 'five' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2'); |
| |
| -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the query 'select a, b, c from a' to be recompiled |
| create index ix1 on a(a); |
| drop index ix1; |
| commit; |
| |
| get cursor scan_cursor as |
| 'select a, b, c from a'; |
| call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 6: heap cursor scan, group fetch = 1 (scan with "<" qualifier) |
| -- Test "less than" qualified cursor scan on a data set. |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -- |
| -- At this point the table should look like: |
| -- 1, 10, 'one' |
| -- 3, 30, 'three' |
| -- 5, 50, 'five' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1'); |
| get cursor scan_cursor as |
| 'select a, b, c from a where a < 3'; |
| call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 7: heap cursor scan, group fetch = 2 (scan with "<" qualifier) |
| -- Test "less than" qualified cursor scan on a data set. |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -- |
| -- At this point the table should look like: |
| -- 1, 10, 'one' |
| -- 3, 30, 'three' |
| -- 5, 50, 'five' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2'); |
| |
| -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the statement to be recompiled |
| create index ix1 on a(a); |
| drop index ix1; |
| commit; |
| |
| get cursor scan_cursor as |
| 'select a, b, c from a where a < 3'; |
| call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 8: heap cursor scan, group fetch = 1 (scan with equals qualifier) |
| -- Test "equals" qualified cursor scan on a data set. |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -- |
| -- At this point the table should look like: |
| -- 1, 10, 'one' |
| -- 3, 30, 'three' |
| -- 5, 50, 'five' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1'); |
| get cursor scan_cursor as |
| 'select a, b, c from a where a = 5'; |
| call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 9: heap cursor scan, group fetch = 2 (scan with equals qualifier) |
| -- Test "equals" qualified cursor scan on a data set. |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -- |
| -- At this point the table should look like: |
| -- 1, 10, 'one' |
| -- 3, 30, 'three' |
| -- 5, 50, 'five' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','2'); |
| get cursor scan_cursor as |
| 'select a, b, c from a where a = 7'; |
| call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault', '16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| |
| -------------------------------------------------------------------------------- |
| -- TEST 10: heap cursor scan, group fetch = 1 (equals qualifier, no rows return) |
| -- Test "equals" qualified cursor scan on a data set, no rows returned. |
| -- SERIALIZABLE - will get table level S lock. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release |
| -- READ UNCOMMITTED - TABLE IS, no row locks. |
| -- |
| -- At this point the table should look like: |
| -- 1, 10, 'one' |
| -- 3, 30, 'three' |
| -- 5, 50, 'five' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','1'); |
| |
| -- RESOLVE: missing row locks |
| -- WORKAROUND: creating an index and dropping it |
| -- to force the statement to be recompiled |
| create index ix1 on a(a); |
| drop index ix1; |
| commit; |
| |
| get cursor scan_cursor as |
| 'select a, b, c from a where a = 7'; |
| CALL SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.language.bulkFetchDefault','16'); |
| |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| next scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| close scan_cursor; |
| select * from lock_table order by tabname, type desc, mode, cnt, lockname; |
| |
| commit; |
| |
| drop table a; |
