You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-commits@db.apache.org by da...@apache.org on 2013/08/30 18:03:33 UTC
svn commit: r1519008 - in
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests:
master/DB2IsolationLevels.out tests/lang/DB2IsolationLevels.sql
tests/lang/DB2IsolationLevelsTest.java tests/lang/_Suite.java
Author: dag
Date: Fri Aug 30 16:03:33 2013
New Revision: 1519008
URL: http://svn.apache.org/r1519008
Log:
DERBY-6276: Convert lang/DB2IsolationLevels.sql to JUnit
Patch which performs the conversion.
Added:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevelsTest.java
Modified:
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DB2IsolationLevels.out
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevels.sql
db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DB2IsolationLevels.out
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DB2IsolationLevels.out?rev=1519008&r1=1519007&r2=1519008&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DB2IsolationLevels.out (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/DB2IsolationLevels.out Fri Aug 30 16:03:33 2013
@@ -1,578 +0,0 @@
-ij> --
--- 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.
---
--- single user test for the various isolation levels
--- also notice in the runtimestatistics output that Fetch Size is 16 for various isolation levels ie we are doing bulk fetch for all isolation levels
--- this will test the fix for bug 5953 - which is to enable bulk fetching for RR and serializable isolation levels as well.
-prepare getIsolation as 'values current isolation';
-ij> autocommit off;
-ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
-0 rows inserted/updated/deleted
-ij> maximumdisplaywidth 2000;
-ij> -- create a table
-create table t1(c1 int not null constraint asdf primary key);
-0 rows inserted/updated/deleted
-ij> commit;
-ij> -- insert a row
-insert into t1 values 1;
-1 row inserted/updated/deleted
-ij> -- verify table scan gets row lock at read committed
-select * from t1;
-C1
------------
-1
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
-Statement Name:
- null
-Statement Text:
- -- verify table scan gets row lock at read committed
-select * from t1
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text:
-Index Scan ResultSet for T1 using constraint ASDF at read committed isolation level using instantaneous share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
-scan information:
- Bit set of columns fetched={0}
- Number of columns fetched=1
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
- None
-ij> -- verify SET ISOLATION commits and changes isolation level
-set isolation RR;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-RR
-ij> -- rollback should find nothing to undo
-rollback;
-ij> select * from t1;
-C1
------------
-1
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
-Statement Name:
- null
-Statement Text:
- select * from t1
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text:
-Index Scan ResultSet for T1 using constraint ASDF at serializable isolation level using share table locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
-scan information:
- Bit set of columns fetched={0}
- Number of columns fetched=1
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
- None
-ij> set isolation reset;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-ij> -- verify SET ISOLATION commits and changes isolation level
-set isolation read committed;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-CS
-ij> -- rollback should find nothing to undo
-rollback;
-ij> select * from t1;
-C1
------------
-1
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
-Statement Name:
- null
-Statement Text:
- select * from t1
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text:
-Index Scan ResultSet for T1 using constraint ASDF at read committed isolation level using instantaneous share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
-scan information:
- Bit set of columns fetched={0}
- Number of columns fetched=1
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
- None
-ij> set current isolation = reset;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-ij> -- verify SET ISOLATION commits and changes isolation level
-set current isolation = RS;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-RS
-ij> -- rollback should find nothing to undo
-rollback;
-ij> select * from t1;
-C1
------------
-1
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
-Statement Name:
- null
-Statement Text:
- select * from t1
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text:
-Index Scan ResultSet for T1 using constraint ASDF at repeatable read isolation level using share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
-scan information:
- Bit set of columns fetched={0}
- Number of columns fetched=1
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
- None
-ij> set isolation to reset;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-ij> -- verify SET ISOLATION commits and changes isolation level
-set isolation = dirty read;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-UR
-ij> -- rollback should find nothing to undo
-rollback;
-ij> select * from t1;
-C1
------------
-1
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
-Statement Name:
- null
-Statement Text:
- select * from t1
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text:
-Index Scan ResultSet for T1 using constraint ASDF at read uncommitted isolation level using share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
-scan information:
- Bit set of columns fetched={0}
- Number of columns fetched=1
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
- None
-ij> -- test WITH ISOLATION clause
-set isolation serializable;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-RR
-ij> select * from t1 with CS;
-C1
------------
-1
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
-Statement Name:
- null
-Statement Text:
- select * from t1 with CS
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text:
-Index Scan ResultSet for T1 using constraint ASDF at read committed isolation level using instantaneous share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
-scan information:
- Bit set of columns fetched={0}
- Number of columns fetched=1
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
- None
-ij> set isolation cursor stability;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-CS
-ij> select * from t1 with RR;
-C1
------------
-1
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
-Statement Name:
- null
-Statement Text:
- select * from t1 with RR
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text:
-Index Scan ResultSet for T1 using constraint ASDF at serializable isolation level using share table locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
-scan information:
- Bit set of columns fetched={0}
- Number of columns fetched=1
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
- None
-ij> set isolation serializable;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-RR
-ij> select * from t1 with RS;
-C1
------------
-1
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
-Statement Name:
- null
-Statement Text:
- select * from t1 with RS
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text:
-Index Scan ResultSet for T1 using constraint ASDF at repeatable read isolation level using share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
-scan information:
- Bit set of columns fetched={0}
- Number of columns fetched=1
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
- None
-ij> set current isolation to read committed;
-0 rows inserted/updated/deleted
-ij> execute getIsolation;
-1
-----
-CS
-ij> select * from t1 with ur;
-C1
------------
-1
-ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------
-Statement Name:
- null
-Statement Text:
- select * from t1 with ur
-Parse Time: 0
-Bind Time: 0
-Optimize Time: 0
-Generate Time: 0
-Compile Time: 0
-Execute Time: 0
-Begin Compilation Timestamp : null
-End Compilation Timestamp : null
-Begin Execution Timestamp : null
-End Execution Timestamp : null
-Statement Execution Plan Text:
-Index Scan ResultSet for T1 using constraint ASDF at read uncommitted isolation level using share row locking chosen by the optimizer
-Number of opens = 1
-Rows seen = 1
-Rows filtered = 0
-Fetch Size = 16
- constructor time (milliseconds) = 0
- open time (milliseconds) = 0
- next time (milliseconds) = 0
- close time (milliseconds) = 0
- next time in milliseconds/row = 0
-scan information:
- Bit set of columns fetched={0}
- Number of columns fetched=1
- Number of deleted rows visited=0
- Number of pages visited=1
- Number of rows qualified=1
- Number of rows visited=1
- Scan type=btree
- Tree height=1
- start position:
- None
- stop position:
- None
- qualifiers:
- None
-ij> -- unknown isolation level
-select * from t1 with rw;
-ERROR 42X01: Syntax error: Encountered "rw" at line 2, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with DIRTY READ;
-ERROR 42X01: Syntax error: Encountered "DIRTY" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with READ UNCOMMITTED;
-ERROR 42X01: Syntax error: Encountered "READ" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with READ COMMITTED;
-ERROR 42X01: Syntax error: Encountered "READ" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with CURSOR STABILITY;
-ERROR 42X01: Syntax error: Encountered "CURSOR" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with REPEATABLE READ;
-ERROR 42X01: Syntax error: Encountered "REPEATABLE" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> select * from t1 with SERIALIZABLE;
-ERROR 42X01: Syntax error: Encountered "SERIALIZABLE" at line 1, column 23.
-Issue the 'help' command for general information on IJ command syntax.
-Any unrecognized commands are treated as potential SQL commands and executed directly.
-Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.
-ij> -- check the db2 isolation levels can be used as identifiers
-create table db2iso(cs int, rr int, ur int, rs int);
-0 rows inserted/updated/deleted
-ij> select cs, rr, ur, rs from db2iso;
-CS |RR |UR |RS
------------------------------------------------
-ij> -- cleanup
-drop table t1;
-0 rows inserted/updated/deleted
-ij>
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevels.sql
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevels.sql?rev=1519008&r1=1519007&r2=1519008&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevels.sql (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevels.sql Fri Aug 30 16:03:33 2013
@@ -1,110 +0,0 @@
---
--- 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.
---
--- single user test for the various isolation levels
--- also notice in the runtimestatistics output that Fetch Size is 16 for various isolation levels ie we are doing bulk fetch for all isolation levels
--- this will test the fix for bug 5953 - which is to enable bulk fetching for RR and serializable isolation levels as well.
-prepare getIsolation as 'values current isolation';
-
-autocommit off;
-call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
-maximumdisplaywidth 2000;
-
--- create a table
-create table t1(c1 int not null constraint asdf primary key);
-commit;
-
--- insert a row
-insert into t1 values 1;
--- verify table scan gets row lock at read committed
-select * from t1;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
--- verify SET ISOLATION commits and changes isolation level
-set isolation RR;
-execute getIsolation;
-
--- rollback should find nothing to undo
-rollback;
-select * from t1;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set isolation reset;
-execute getIsolation;
--- verify SET ISOLATION commits and changes isolation level
-set isolation read committed;
-execute getIsolation;
--- rollback should find nothing to undo
-rollback;
-select * from t1;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set current isolation = reset;
-execute getIsolation;
-
--- verify SET ISOLATION commits and changes isolation level
-set current isolation = RS;
-execute getIsolation;
--- rollback should find nothing to undo
-rollback;
-select * from t1;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set isolation to reset;
-execute getIsolation;
-
--- verify SET ISOLATION commits and changes isolation level
-set isolation = dirty read;
-execute getIsolation;
--- rollback should find nothing to undo
-rollback;
-select * from t1;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
--- test WITH ISOLATION clause
-set isolation serializable;
-execute getIsolation;
-select * from t1 with CS;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set isolation cursor stability;
-execute getIsolation;
-select * from t1 with RR;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set isolation serializable;
-execute getIsolation;
-select * from t1 with RS;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
-set current isolation to read committed;
-execute getIsolation;
-select * from t1 with ur;
-values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
-
--- unknown isolation level
-select * from t1 with rw;
-select * from t1 with DIRTY READ;
-select * from t1 with READ UNCOMMITTED;
-select * from t1 with READ COMMITTED;
-select * from t1 with CURSOR STABILITY;
-select * from t1 with REPEATABLE READ;
-select * from t1 with SERIALIZABLE;
-
--- check the db2 isolation levels can be used as identifiers
-create table db2iso(cs int, rr int, ur int, rs int);
-select cs, rr, ur, rs from db2iso;
--- cleanup
-drop table t1;
Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevelsTest.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevelsTest.java?rev=1519008&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevelsTest.java (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/DB2IsolationLevelsTest.java Fri Aug 30 16:03:33 2013
@@ -0,0 +1,272 @@
+/**
+ * Derby - Class org.apache.derbyTesting.functionTests.tests.lang.DB2IsolationLevelsTest
+ *
+ * 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.
+ */
+package org.apache.derbyTesting.functionTests.tests.lang;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Statement;
+import junit.framework.Test;
+import junit.framework.TestSuite;
+import org.apache.derbyTesting.junit.BaseJDBCTestCase;
+import org.apache.derbyTesting.junit.JDBC;
+import org.apache.derbyTesting.junit.SQLUtilities;
+import org.apache.derbyTesting.junit.TestConfiguration;
+
+
+public final class DB2IsolationLevelsTest extends BaseJDBCTestCase {
+
+ /**
+ * Public constructor required for running test as standalone JUnit.
+ */
+ public DB2IsolationLevelsTest(String name)
+ {
+ super(name);
+ }
+
+ public static Test suite()
+ {
+ TestSuite suite = new TestSuite("DB2IsolationLevelsTest Test");
+ suite.addTest(TestConfiguration.defaultSuite(
+ DB2IsolationLevelsTest.class));
+ return suite;
+ }
+
+ @Override
+ public void setUp() throws Exception {
+ super.setUp();
+ // Create a table
+ createStatement().executeUpdate(
+ "create table t1(c1 int not null constraint asdf primary key)");
+ }
+
+ @Override
+ public void tearDown() throws Exception {
+ createStatement().executeUpdate("drop table t1");
+ super.tearDown();
+ }
+
+ public void test_DB2IsolationLevelsTest() throws Exception
+ {
+ final Statement st = createStatement();
+ final PreparedStatement ps =
+ prepareStatement("values current isolation");
+
+ setAutoCommit(false);
+ st.executeUpdate("call syscs_util.syscs_set_runtimestatistics(1)");
+ st.executeUpdate("insert into t1 values 1");
+
+ /* -----------------------------------------------------------------
+ * Session isolation default: (CS) read committed
+ */
+ assertQueryResult(st.executeQuery("select * from t1"));
+ assertPlan(
+ st,
+ "Index Scan ResultSet for T1 using constraint ASDF at " +
+ "read committed isolation level using instantaneous share " +
+ "row locking chosen by the optimizer");
+ assertIsolation(ps, "CS");
+
+ /* -----------------------------------------------------------------
+ * Session isolation: RR (serializable)
+ */
+ st.executeUpdate("set isolation RR");
+ assertIsolation(ps, "RR");
+ // Rollback should find nothing to do
+ rollback();
+
+ assertQueryResult(st.executeQuery("select * from t1"));
+ assertPlan(
+ st,
+ "Index Scan ResultSet for T1 using constraint ASDF at " +
+ "serializable isolation level using share table locking " +
+ "chosen by the optimizer");
+
+ /* -----------------------------------------------------------------
+ * Set isolation back to default
+ */
+ st.executeUpdate("set isolation reset");
+ assertIsolation(ps, " ");
+
+ /* -----------------------------------------------------------------
+ * Session isolation: CS (read committed)
+ */
+ st.executeUpdate("set isolation read committed");
+ assertIsolation(ps, "CS");
+ // rollback should find nothing to undo
+ rollback();
+
+ assertQueryResult(st.executeQuery("select * from t1"));
+ assertPlan(
+ st,
+ "Index Scan ResultSet for T1 using constraint ASDF at " +
+ "read committed isolation level using instantaneous share " +
+ "row locking chosen by the optimizer");
+
+ /* -----------------------------------------------------------------
+ * Set isolation back to default
+ */
+ st.executeUpdate("set isolation to reset");
+ assertIsolation(ps, " ");
+
+ /* -----------------------------------------------------------------
+ * Session isolation: RS (read committed)
+ */
+ st.executeUpdate("set current isolation = RS");
+ assertIsolation(ps, "RS");
+ // rollback should find nothing to undo
+ rollback();
+
+ assertQueryResult(st.executeQuery("select * from t1"));
+ assertPlan(
+ st,
+ "Index Scan ResultSet for T1 using constraint ASDF at " +
+ "repeatable read isolation level using share row locking " +
+ "chosen by the optimizer");
+
+ /* -----------------------------------------------------------------
+ * Set isolation back to default
+ */
+ st.executeUpdate("set isolation reset");
+ assertIsolation(ps, " ");
+
+ /* -----------------------------------------------------------------
+ * Session isolation: UR (dirty read)
+ */
+ st.executeUpdate("set isolation = dirty read");
+ assertIsolation(ps, "UR");
+ // rollback should find nothing to undo
+ rollback();
+
+ assertQueryResult(st.executeQuery("select * from t1"));
+ assertPlan(
+ st,
+ "Index Scan ResultSet for T1 using constraint ASDF at " +
+ "read uncommitted isolation level using share row locking " +
+ "chosen by the optimizer");
+
+ /* -----------------------------------------------------------------
+ * Session isolation: RR (serializable)
+ */
+ st.executeUpdate("set isolation serializable");
+ assertIsolation(ps, "RR");
+
+ /*
+ * Override session serializable (RR) with read committed (CS)
+ * on statement level
+ */
+ assertQueryResult(st.executeQuery("select * from t1 with CS"));
+ assertPlan(
+ st,
+ "Index Scan ResultSet for T1 using constraint ASDF at " +
+ "read committed isolation level using instantaneous share " +
+ "row locking chosen by the optimizer");
+
+ /* -----------------------------------------------------------------
+ * Session isolation: CS (read committed)
+ */
+ st.executeUpdate("set isolation cursor stability");
+ assertIsolation(ps, "CS");
+
+ /*
+ * Override session read committed (CS) with serializable (RR)
+ * on statement level
+ */
+ assertQueryResult(st.executeQuery("select * from t1 with RR"));
+ assertPlan(
+ st,
+ "Index Scan ResultSet for T1 using constraint ASDF at " +
+ "serializable isolation level using share table locking " +
+ "chosen by the optimizer");
+
+ /* -----------------------------------------------------------------
+ * Session isolation: RR (serializable)
+ */
+ st.executeUpdate("set isolation serializable");
+ assertIsolation(ps, "RR");
+ /*
+ * Override session RR (serializable) with repeatable read (RS)
+ * on statement level
+ */
+ assertQueryResult(st.executeQuery("select * from t1 with RS"));
+ assertPlan(
+ st,
+ "Index Scan ResultSet for T1 using constraint ASDF at " +
+ "repeatable read isolation level using share row locking " +
+ "chosen by the optimizer");
+
+ /* -----------------------------------------------------------------
+ * Session isolation: CS (read committed)
+ */
+ st.executeUpdate("set current isolation to read committed");
+ assertIsolation(ps, "CS");
+ /*
+ * Override session CS (read committed) with UR (read uncommitted)
+ * on statement level
+ */
+ assertQueryResult(st.executeQuery("select * from t1 with ur"));
+ assertPlan(
+ st,
+ "Index Scan ResultSet for T1 using constraint ASDF at " +
+ "read uncommitted isolation level using share row locking " +
+ "chosen by the optimizer");
+
+ /* -----------------------------------------------------------------
+ * Unknown isolation levels: expect syntax errors
+ */
+ final String e = "42X01"; // syntax error
+ assertStatementError(e, st, "select * from t1 with rw");
+ assertStatementError(e, st, "select * from t1 with dirty read");
+ assertStatementError(e, st, "select * from t1 with read uncommitted");
+ assertStatementError(e, st, "select * from t1 with read committed");
+ assertStatementError(e, st, "select * from t1 with cursor stability");
+ assertStatementError(e, st, "select * from t1 with repeatable read");
+ assertStatementError(e, st, "select * from t1 with serializable");
+
+ /* -----------------------------------------------------------------
+ * Check the db2 isolation levels can be used as identifiers
+ */
+ st.executeUpdate("create table db2iso(cs int, rr int, ur int, rs int)");
+ ResultSet rs = st.executeQuery("select cs, rr, ur, rs from db2iso");
+ JDBC.assertEmpty(rs);
+ rollback();
+ }
+
+ private void assertQueryResult(ResultSet rs) throws SQLException {
+ JDBC.assertColumnNames(rs, new String [] {"C1"});
+ JDBC.assertFullResultSet(rs, new String [][]{{"1"}}, true);
+ rs.close();
+ }
+
+ private void assertPlan(Statement s, String expected)
+ throws SQLException {
+ SQLUtilities.getRuntimeStatisticsParser(s).
+ assertSequence(new String[]{expected});
+ }
+
+ private void assertIsolation(PreparedStatement p, String expected)
+ throws SQLException {
+ ResultSet rs = p.executeQuery();
+ JDBC.assertColumnNames(rs, new String [] {"1"});
+ JDBC.assertFullResultSet(rs, new String [][]{{expected}}, true);
+ rs.close();
+ }
+}
Modified: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java
URL: http://svn.apache.org/viewvc/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java?rev=1519008&r1=1519007&r2=1519008&view=diff
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java (original)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/_Suite.java Fri Aug 30 16:03:33 2013
@@ -242,6 +242,7 @@ public class _Suite extends BaseTestCase
suite.addTest(XMLOptimizerTraceTest.suite());
suite.addTest(MergeStatementTest.suite());
suite.addTest(ConstraintCharacteristicsTest.suite());
+ suite.addTest(DB2IsolationLevelsTest.suite());
return suite;
}
}