| /* |
| * Copyright (c) 1998, 2021 Oracle and/or its affiliates. All rights reserved. |
| * |
| * This program and the accompanying materials are made available under the |
| * terms of the Eclipse Public License v. 2.0 which is available at |
| * http://www.eclipse.org/legal/epl-2.0, |
| * or the Eclipse Distribution License v. 1.0 which is available at |
| * http://www.eclipse.org/org/documents/edl-v10.php. |
| * |
| * SPDX-License-Identifier: EPL-2.0 OR BSD-3-Clause |
| */ |
| |
| // Contributors: |
| // Created July 9, 2008 - ailitchev |
| // bug 240210: Tests: Several LRG tests hang on Sybase |
| // Changed Dec 17, 2008 - etang |
| // Move the class from org.eclipse.persistence.testing.tests.unitofwork |
| // to org.eclipse.persistence.testing.framework |
| package org.eclipse.persistence.testing.framework; |
| |
| import java.sql.Connection; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.util.HashMap; |
| |
| import org.eclipse.persistence.internal.databaseaccess.DatabaseAccessor; |
| import org.eclipse.persistence.sessions.SessionEvent; |
| import org.eclipse.persistence.sessions.SessionEventAdapter; |
| import org.eclipse.persistence.sessions.server.ServerSession; |
| import org.eclipse.persistence.testing.framework.TestProblemException; |
| |
| /* |
| * According to http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/53713;pt=52735/* |
| * The SQL92 standard defines four levels of isolation for transactions. |
| * Each isolation level specifies the kinds of actions that are not permitted while concurrent transactions are executing. |
| * Higher levels include the restrictions imposed by the lower levels: |
| * |
| * Level 0 - ensures that data written by one transaction represents the actual data. |
| * It prevents other transactions from changing data that has already been modified |
| * (through an insert, delete, update, and so on) by an uncommitted transaction. |
| * The other transactions are blocked from modifying that data until the transaction commits. |
| * However, other transactions can still read the uncommitted data, which results in dirty reads. |
| * |
| * Level 1 - prevents dirty reads. |
| * Such reads occur when one transaction modifies a row, and a second transaction reads that row before the first transaction commits the change. |
| * If the first transaction rolls back the change, the information read by the second transaction becomes invalid. |
| * This is the default isolation level supported by Adaptive Server. |
| * .... |
| * |
| * Apparently Sybase versions 12.5 and 15 has Level 1 set as default. |
| * That causes several tests to hang: these tests begin transaction, update a row, |
| * then (before the transaction has been committed) attempt to read the row through another connection. |
| * |
| * To allow these reads to go through (and read the uncommitted data) connection isolation level |
| * should be temporary switched to Level 0. |
| * |
| * This class switches the acquired connection to Level 0 and then sets back the original isolation level before connection is released. |
| * |
| * Note that for the above scenario only read connection require Level 0. |
| * |
| * This solution works only on Sybase 15 but fails on Sybase 12.5 with: |
| * com.sybase.jdbc3.jdbc.SybSQLException: The optimizer could not find a unique index which it could use to perform an isolation level 0 scan on table 'RESPONS'. |
| * |
| */ |
| public class SybaseTransactionIsolationListener extends SessionEventAdapter { |
| HashMap<Connection, Integer> connections = new HashMap<Connection, Integer>(); |
| public static int requiredVersion = 15; |
| // verify that it's version 15 or higher - this doesn't work with version 12.5 |
| public static boolean isDatabaseVersionSupported(ServerSession serverSession) { |
| DatabaseAccessor accessor = (DatabaseAccessor)serverSession.allocateReadConnection(); |
| int version; |
| try { |
| String strVersion = accessor.getConnectionMetaData().getDatabaseProductVersion(); |
| int iStart = strVersion.indexOf("/") + 1; |
| int iEnd = strVersion.indexOf("."); |
| String strIntVersion = strVersion.substring(iStart, iEnd); |
| version = Integer.parseInt(strIntVersion); |
| } catch (SQLException ex) { |
| throw new TestProblemException("failed to obtain database version number", ex); |
| } |
| return version >= requiredVersion; |
| } |
| @Override |
| public void postAcquireConnection(SessionEvent event) { |
| Connection conn = ((DatabaseAccessor)event.getResult()).getConnection(); |
| Statement stmt1 = null; |
| Statement stmt2 = null; |
| ResultSet result = null; |
| Integer isolationLevel; |
| try { |
| stmt1 = conn.createStatement(); |
| result = stmt1.executeQuery("select @@isolation"); |
| result.next(); |
| isolationLevel = result.getInt(1); |
| if(isolationLevel > 0) { |
| // If conn1 began transaction and updated the row (but hasn't committed the transaction yet), |
| // then conn2 should be able to read the original (not updated) state of the row. |
| // Without this conn2 reading the row hangs on Sybase. |
| stmt2 = conn.createStatement(); |
| stmt2.execute("set transaction isolation level 0"); |
| stmt2.close(); |
| |
| connections.put(conn, isolationLevel); |
| |
| } |
| } catch (SQLException sqlException) { |
| throw new TestProblemException("postAcquireConnection failed. ", sqlException); |
| } finally { |
| if(result != null) { |
| try { |
| result.close(); |
| } catch (SQLException ex) { |
| // Ignore |
| } |
| } |
| if(stmt1 != null) { |
| try { |
| stmt1.close(); |
| } catch (SQLException ex) { |
| // Ignore |
| } |
| } |
| if(stmt2 != null) { |
| try { |
| stmt2.close(); |
| } catch (SQLException ex) { |
| // Ignore |
| } |
| } |
| } |
| } |
| @Override |
| public void preReleaseConnection(SessionEvent event) { |
| Connection conn = ((DatabaseAccessor)event.getResult()).getConnection(); |
| Statement stmt = null; |
| try { |
| Integer isolationLevel = connections.remove(conn); |
| if (isolationLevel != null){ |
| // reset the original transaction isolation. |
| stmt = conn.createStatement(); |
| stmt.execute("set transaction isolation level " + isolationLevel); |
| stmt.close(); |
| } |
| } catch (SQLException sqlException) { |
| throw new TestProblemException("preReleaseConnection failed. ", sqlException); |
| } finally { |
| if(stmt != null) { |
| try { |
| stmt.close(); |
| } catch (SQLException ex) { |
| // Ignore |
| } |
| } |
| } |
| } |
| } |