| 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 whether the RllRAMAccessmanager is working right (ie. forcing row |
| -- level locking with no properties set). |
| 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)> create table heap_only (a int); |
| 0 rows inserted/updated/deleted |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> -------------------------------------------------------------------------------- |
| -- Test insert into empty heap, should just get row lock |
| -------------------------------------------------------------------------------- |
| insert into heap_only values (1); |
| 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 |HEAP_ONLY |Tablelock |GRANT|ACTIVE |
| APP |UserTransaction|ROW |1 |X |HEAP_ONLY |(1,7) |GRANT|ACTIVE |
| ij(CONNECTION1)> commit; |
| ij(CONNECTION1)> |