| -- |
| -- 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 in "set" queries 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 scan. |
| -- TEST 1: heap scan, some rows deleted. |
| -- TEST 2: heap scan, (scan with "<" qualifier) |
| -- TEST 3: heap scan, (scan with equals qualifier) |
| -- TEST 4: heap scan, (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 scan. |
| -- Test full cursor scan which does no updates. |
| -- SERIALIZABLE - will get table level S lock, held to end of xact. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). held to end of xact. |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release. No |
| -- locks held when statement completes. |
| -- READ UNCOMMITTED - TABLE IS, no row locks. No locks after statement ends. |
| -------------------------------------------------------------------------------- |
| |
| select a, b, c from a; |
| |
| 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 scan, some rows deleted. |
| -- 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. |
| -- SERIALIZABLE - will get table level S lock, held to end of xact. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). held to end of xact. |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release. No |
| -- locks held when statement completes. |
| -- READ UNCOMMITTED - TABLE IS, no row locks. No locks after statement ends. |
| -- |
| -- 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(); |
| |
| select a, b, c from a; |
| 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 scan, (scan with "<" qualifier) |
| -- Test "less than" qualified cursor scan on a data set. |
| -- SERIALIZABLE - will get table level S lock, held to end of xact. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). held to end of xact. |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release. No |
| -- locks held when statement completes. |
| -- READ UNCOMMITTED - TABLE IS, no row locks. No locks after statement ends. |
| -- |
| -- At this point the table should look like: |
| -- 1, 10, 'one' |
| -- 3, 30, 'three' |
| -- 5, 50, 'five' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| |
| select a, b, c from a; |
| 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 scan, (scan with equals qualifier) |
| -- Test "equals" qualified cursor scan on a data set. |
| -- SERIALIZABLE - will get table level S lock, held to end of xact. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). held to end of xact. |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release. No |
| -- locks held when statement completes. |
| -- READ UNCOMMITTED - TABLE IS, no row locks. No locks after statement ends. |
| -- |
| -- At this point the table should look like: |
| -- 1, 10, 'one' |
| -- 3, 30, 'three' |
| -- 5, 50, 'five' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| |
| select a, b, c from a where a = 5; |
| 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 scan, (equals qualifier, no rows return) |
| -- Test "equals" qualified cursor scan on a data set, no rows returned. |
| -- SERIALIZABLE - will get table level S lock, held to end of xact. |
| -- REPEATABLE READ - TABLE IS, will get row S locks as it visits each row |
| -- (including deleted ones). held to end of xact. |
| -- READ COMMITTED - TABLE IS, will get instantaneous locks and release. No |
| -- locks held when statement completes. |
| -- READ UNCOMMITTED - TABLE IS, no row locks. No locks after statement ends. |
| -- |
| -- At this point the table should look like: |
| -- 1, 10, 'one' |
| -- 3, 30, 'three' |
| -- 5, 50, 'five' |
| -- 7, 70, 'seven' |
| -------------------------------------------------------------------------------- |
| |
| select a, b, c from a where a = 7; |
| |
| 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; |
| |
| drop table a; |