001: /*
002:
003: Derby - Class org.apache.derbyTesting.functionTests.tests.jdbcapi.setTransactionIsolation.java
004:
005: Licensed to the Apache Software Foundation (ASF) under one or more
006: contributor license agreements. See the NOTICE file distributed with
007: this work for additional information regarding copyright ownership.
008: The ASF licenses this file to You under the Apache License, Version 2.0
009: (the "License"); you may not use this file except in compliance with
010: the License. You may obtain a copy of the License at
011:
012: http://www.apache.org/licenses/LICENSE-2.0
013:
014: Unless required by applicable law or agreed to in writing, software
015: distributed under the License is distributed on an "AS IS" BASIS,
016: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
017: See the License for the specific language governing permissions and
018: limitations under the License.
019:
020: */
021:
022: package org.apache.derbyTesting.functionTests.tests.jdbcapi;
023:
024: import java.lang.reflect.*;
025:
026: import java.sql.CallableStatement;
027: import java.sql.Connection;
028: import java.sql.PreparedStatement;
029: import java.sql.ResultSet;
030: import java.sql.ResultSetMetaData;
031: import java.sql.SQLException;
032: import java.sql.Statement;
033: import java.sql.Types;
034: import java.sql.*;
035: import java.io.*;
036:
037: import org.apache.derby.tools.ij;
038: import org.apache.derby.tools.JDBCDisplayUtil;
039: import org.apache.derbyTesting.functionTests.util.TestUtil;
040:
041: public class setTransactionIsolation {
042:
043: static String conntype = null;
044: static boolean shortTest = true;
045:
046: public static void main(String args[]) {
047:
048: try {
049: // use the ij utility to read the property file and
050: // make the initial connection.
051: ij.getPropertyArg(args);
052: Connection conn = ij.startJBMS();
053:
054: createAndPopulateTable(conn);
055: runTests(conn);
056: conn.rollback();
057: cleanUp(conn);
058: conn.close();
059: } catch (Throwable e) {
060: e.printStackTrace();
061: }
062: }
063:
064: private static void dropTable(Statement stmt, String tab) {
065: try {
066: stmt.executeUpdate("drop table " + tab);
067: } catch (SQLException se) {
068: }
069: }
070:
071: //create table and insert couple of rows
072: private static void createAndPopulateTable(Connection conn)
073: throws SQLException {
074: Statement stmt = conn.createStatement();
075:
076: String[] tabsToDrop = { "tab1", "t1", "t1copy", "t2", "t3" };
077: for (int i = 0; i < tabsToDrop.length; i++)
078: dropTable(stmt, tabsToDrop[i]);
079:
080: System.out.println("Creating table...");
081: final int stringLength = 400;
082: stmt.executeUpdate("CREATE TABLE TAB1 (c11 int, "
083: + "c12 varchar(" + stringLength + "))");
084: PreparedStatement insertStmt = conn
085: .prepareStatement("INSERT INTO TAB1 VALUES(?,?)");
086: // We need to ensure that there is more data in the table than the
087: // client can fetch in one message (about 32K). Otherwise, the
088: // cursor might be closed on the server and we are not testing the
089: // same thing in embedded mode and client/server mode.
090: final int rows = 40000 / stringLength;
091: StringBuffer buff = new StringBuffer(stringLength);
092: for (int i = 0; i < stringLength; i++) {
093: buff.append(" ");
094: }
095: for (int i = 1; i <= rows; i++) {
096: insertStmt.setInt(1, i);
097: insertStmt.setString(2, buff.toString());
098: insertStmt.executeUpdate();
099: }
100: insertStmt.close();
101:
102: stmt.execute("create table t1(I int, B char(15))");
103: stmt.execute("create table t1copy(I int, B char(15))");
104:
105: stmt.executeUpdate("INSERT INTO T1 VALUES(1,'First Hello')");
106: stmt.executeUpdate("INSERT INTO T1 VALUES(2,'Second Hello')");
107: stmt.executeUpdate("INSERT INTO T1 VALUES(3,'Third Hello')");
108:
109: stmt.executeUpdate("create table t3 (i integer)");
110:
111: System.out.println("done creating table and inserting data.");
112:
113: stmt.close();
114: }
115:
116: public static void runTests(Connection conn) throws Throwable {
117: try {
118: // make new statements after we set the isolation level
119: testIsolation(conn, true);
120: // reuse old statements. setTransaction isolation has no effect
121: // on already prepared statements for network server
122: conn
123: .setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
124: testIsolation(conn, false);
125: testSetTransactionIsolationInHoldCursor(conn);
126: testSetTransactionIsolationCommits(conn);
127: } catch (SQLException sqle) {
128: System.out.print("FAIL:");
129: org.apache.derby.tools.JDBCDisplayUtil.ShowSQLException(
130: System.out, sqle);
131: sqle.printStackTrace(System.out);
132: }
133:
134: }
135:
136: public static int[] isoLevels = {
137: Connection.TRANSACTION_READ_UNCOMMITTED,
138: Connection.TRANSACTION_REPEATABLE_READ,
139: Connection.TRANSACTION_READ_COMMITTED,
140: Connection.TRANSACTION_SERIALIZABLE };
141:
142: private static void testIsolation(Connection conn,
143: boolean makeNewStatements) throws SQLException {
144:
145: Connection conn2 = null;
146: try {
147: conn2 = ij.startJBMS();
148:
149: } catch (Exception e) {
150: System.out.println(e.getMessage());
151: e.printStackTrace();
152: }
153: Statement stmt = conn.createStatement();
154: Statement stmt2 = conn2.createStatement();
155: System.out.println("*** testIsolation. makeNewStatements ="
156: + makeNewStatements);
157:
158: conn.setAutoCommit(false);
159:
160: conn2.setAutoCommit(false);
161:
162: stmt2
163: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
164: String[] sql = { "select * from t1",
165: "insert into t1copy (select * from t1)" };
166:
167: PreparedStatement ps = null;
168: System.out.println("*** Test with no lock timeouts ***");
169: for (int s = 0; s < sql.length; s++)
170: testLevelsAndPrintStatistics(conn2, sql[s],
171: makeNewStatements);
172: // Now do an insert to create lock timeout
173: System.out
174: .println("*** Test with lock timeouts on everything but read uncommitted***");
175: System.out
176: .println("conn :insert into t1 values(4,'Forth Hello') (no commit)");
177: stmt.executeUpdate("insert into t1 values(4,'Fourth Hello')");
178: for (int s = 0; s < sql.length; s++)
179: testLevelsAndPrintStatistics(conn2, sql[s],
180: makeNewStatements);
181: stmt.close();
182: stmt2.close();
183: // rollback to cleanup locks from insert
184: conn.rollback();
185:
186: }
187:
188: /**
189: * Call setTransactionIsolation with holdable cursor open?
190: */
191: public static void testSetTransactionIsolationInHoldCursor(
192: Connection conn) {
193: try {
194:
195: PreparedStatement ps = conn
196: .prepareStatement("SELECT * from TAB1");
197: ResultSet rs = ps.executeQuery();
198: rs.next();
199: // setTransactionIsolation should fail because we have
200: // a holdable cursor open
201: conn
202: .setTransactionIsolation(java.sql.Connection.TRANSACTION_SERIALIZABLE);
203: rs.next(); // to fix DERBY-1108. Else the GC for ibm15 will clean up the ResultSet Object
204: } catch (SQLException se) {
205: System.out.println("EXPECTED EXCEPTION SQLSTATE:"
206: + se.getSQLState() + " " + se.getMessage());
207: return;
208: }
209: System.out
210: .println("FAIL: setTransactionIsolation() did not throw exception with open hold cursor");
211: }
212:
213: /**
214: * setTransactionIsolation commits?
215: */
216: public static void testSetTransactionIsolationCommits(
217: Connection conn) {
218: // In the current client implementation, the transaction will
219: // commit when setTransactionIsolation is called, while the
220: // embedded driver will not commit. See
221: // http://issues.apache.org/jira/browse/DERBY-2064
222: try {
223: conn.rollback();
224: conn.setAutoCommit(false);
225: conn
226: .setTransactionIsolation(java.sql.Connection.TRANSACTION_SERIALIZABLE);
227: Statement s = conn.createStatement();
228: s.executeUpdate("delete from t3");
229: s.executeUpdate("insert into t3 values(1)");
230: conn.commit();
231: s.executeUpdate("insert into t3 values(2)");
232: conn
233: .setTransactionIsolation(java.sql.Connection.TRANSACTION_SERIALIZABLE);
234: conn.rollback();
235: ResultSet rs = s.executeQuery("select count(*) from t3");
236: rs.next();
237: int count = rs.getInt(1);
238: switch (count) {
239: case 1:
240: System.out
241: .println("count="
242: + count
243: + ", setTransactionIsolation() does not commit");
244: break;
245: case 2:
246: System.out.println("count=" + count
247: + ", setTransactionIsolation() commits");
248: break;
249: default:
250: System.out
251: .println("FAIL: count="
252: + count
253: + ", unexepected behaviour from testSetTransactionIsolationCommits");
254: break;
255: }
256: rs.close();
257: s.close();
258: } catch (SQLException se) {
259: System.out.println(se.getMessage());
260: }
261: }
262:
263: public static void testLevelsAndPrintStatistics(Connection con,
264: String sql, boolean makeNewStatements) throws SQLException {
265: System.out.println("***testLevelsAndPrintStatistics sql:" + sql
266: + " makenewStatements:" + makeNewStatements);
267: PreparedStatement ps = con.prepareStatement(sql);
268: Statement stmt = con.createStatement();
269:
270: System.out.println("con.prepareStatement(" + sql + ")");
271: for (int i = 0; i < isoLevels.length; i++) {
272:
273: try {
274: System.out.println("con.setTransactionIsolation("
275: + getIsoLevelName(isoLevels[i]) + ")");
276: con.setTransactionIsolation(isoLevels[i]);
277:
278: System.out
279: .println("con.getTransactionIsolation() ="
280: + getIsoLevelName(con
281: .getTransactionIsolation()));
282: if (makeNewStatements) {
283: ps.close();
284: ps = con.prepareStatement(sql);
285: System.out.println("con.prepareStatement(" + sql
286: + ")");
287: }
288:
289: System.out.println(sql);
290: ps.execute();
291: ResultSet rs = ps.getResultSet();
292: // fetch data so that we get the same errors with
293: // and without pre-fetching in execute()
294: rs.next();
295: showScanStatistics(rs, con);
296:
297: // Now execute again and look at the locks
298: /*
299: // can't do the locks right now because of prefetch
300: ps.execute();
301: rs = ps.getResultSet();
302: if (rs != null)
303: {
304: rs.next();
305: ResultSet lockrs = stmt.executeQuery("Select * from SYSCS_DIAG.LOCK_TABLE l where l.tableType <> 'S'");
306: JDBCDisplayUtil.DisplayResults(System.out,lockrs,con);
307: lockrs.close();
308: rs.close();
309: }
310: */
311: } catch (Exception e) {
312: System.out.println(e.getMessage());
313: //e.printStackTrace();
314: }
315: con.commit();
316: stmt
317: .executeUpdate("call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1)");
318: }
319:
320: ps.close();
321: stmt.close();
322: System.out.println("\n\n");
323: }
324:
325: public static String getIsoLevelName(int level) {
326: switch (level) {
327: case java.sql.Connection.TRANSACTION_REPEATABLE_READ:
328: return "TRANSACTION_REAPEATABLE_READ:" + level;
329:
330: case java.sql.Connection.TRANSACTION_READ_COMMITTED:
331: return "TRANSACTION_READ_COMMITTED:" + level;
332: case java.sql.Connection.TRANSACTION_SERIALIZABLE:
333: return "TRANSACTION_SERIALIZABLE:" + level;
334: case java.sql.Connection.TRANSACTION_READ_UNCOMMITTED:
335: return "TRANSACTION_READ_UNCOMMITTED:" + level;
336:
337: }
338: return "UNEXPECTED_ISO_LEVEL";
339: }
340:
341: private static void statementExceptionExpected(Statement s,
342: String sql) {
343: System.out.println(sql);
344: try {
345: s.execute(sql);
346: System.out
347: .println("FAIL - SQL expected to throw exception");
348: } catch (SQLException sqle) {
349: System.out.println("EXPECTED " + sqle.toString());
350: }
351: }
352:
353: public static void showResultsAndStatistics(Statement s,
354: Connection conn, boolean expectException) {
355: ResultSet rs = null;
356: try {
357: rs = s.getResultSet();
358: if (rs == null) {
359: System.out
360: .println("UPDATE COUNT " + s.getUpdateCount());
361: return;
362: } else
363: showResultsAndStatistics(rs, conn, expectException);
364: } catch (SQLException se) {
365: // assume the getResultSet should go well
366: // expectException is for the scan
367: System.out.print("FAIL: UNEXPECTED EXCEPTION:");
368: JDBCDisplayUtil.ShowSQLException(System.out, se);
369: }
370: }
371:
372: public static void showResultsAndStatistics(ResultSet rs,
373: Connection conn, boolean expectException) {
374:
375: try {
376: System.out.println("CursorName:" + rs.getCursorName());
377: JDBCDisplayUtil.DisplayResults(System.out, rs, conn);
378: showScanStatistics(rs, conn);
379: } catch (SQLException se) {
380: if (expectException)
381: System.out.print("EXPECTED SQL EXCEPTION:");
382: else
383: System.out.print("FAIL: UNEXPECTED EXCEPTION:");
384: JDBCDisplayUtil.ShowSQLException(System.out, se);
385: }
386:
387: }
388:
389: public static void showScanStatistics(ResultSet rs, Connection conn) {
390: Statement s = null;
391: ResultSet infors = null;
392:
393: try {
394: rs.close(); // need to close to get statistics
395: s = conn.createStatement();
396: infors = s
397: .executeQuery("values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS()");
398: JDBCDisplayUtil.setMaxDisplayWidth(2000);
399: JDBCDisplayUtil.DisplayResults(System.out, infors, conn);
400: infors.close();
401: } catch (SQLException se) {
402: System.out.print("FAIL:");
403: JDBCDisplayUtil.ShowSQLException(System.out, se);
404: }
405: }
406:
407: static void cleanUp(Connection conn) throws SQLException {
408: String[] testObjects = { "table t1" };
409: Statement stmt = conn.createStatement();
410: TestUtil.cleanUpTest(stmt, testObjects);
411: conn.commit();
412: stmt.close();
413: }
414:
415: }
|