Example usage for java.sql Connection setTransactionIsolation

Introduction

In this page you can find the example usage for java.sql Connection setTransactionIsolation.

Prototype

void setTransactionIsolation(int level) throws SQLException;

Source Link

Document

Attempts to change the transaction isolation level for this Connection object to the one given.

Usage

From source file:org.opendatakit.persistence.engine.pgres.TaskLockImpl.java

private TaskLockTable doTransaction(TaskLockTable entity, long l)
        throws ODKEntityNotFoundException, ODKTaskLockException {
    boolean first;

    final List<String> stmts = new ArrayList<String>();

    String uri = entity.getUri();

    StringBuilder stringBuilder = new StringBuilder();
    String tableName = K_BQ + datastore.getDefaultSchemaName() + K_BQ + "." + K_BQ + TaskLockTable.TABLE_NAME
            + K_BQ;/*w w  w  .ja  v  a2s .c  om*/

    stringBuilder.append("'").append(user.getUriUser().replaceAll("'", "''")).append("'");
    String uriUserInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(uri.replaceAll("'", "''")).append("'");
    String uriLockInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(entity.getFormId().replaceAll("'", "''")).append("'");
    String formIdInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("'").append(entity.getTaskType().replaceAll("'", "''")).append("'");
    String taskTypeInline = stringBuilder.toString();
    stringBuilder.setLength(0);
    stringBuilder.append("interval '").append(l).append(" milliseconds'");
    String lifetimeIntervalMilliseconds = stringBuilder.toString();
    stringBuilder.setLength(0);

    stringBuilder.append("LOCK TABLE ").append(tableName).append(" IN ACCESS EXCLUSIVE MODE");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);

    dam.recordPutUsage(TaskLockTable.TABLE_NAME);
    if (!entity.isFromDatabase()) {
        // insert a new record (prospective lock)
        stringBuilder.append("INSERT INTO ");
        stringBuilder.append(tableName);
        stringBuilder.append(" (");
        first = true;
        for (DataField dataField : entity.getFieldList()) {
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            stringBuilder.append(K_BQ);
            stringBuilder.append(dataField.getName());
            stringBuilder.append(K_BQ);
        }
        first = true;
        stringBuilder.append(") VALUES ( ");
        for (DataField dataField : entity.getFieldList()) {
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            if (dataField.equals(entity.creationDate) || dataField.equals(entity.lastUpdateDate)) {
                stringBuilder.append("NOW()");
            } else if (dataField.equals(entity.creatorUriUser) || dataField.equals(entity.lastUpdateUriUser)) {
                stringBuilder.append(uriUserInline);
            } else if (dataField.equals(entity.formId)) {
                stringBuilder.append(formIdInline);
            } else if (dataField.equals(entity.taskType)) {
                stringBuilder.append(taskTypeInline);
            } else if (dataField.equals(entity.primaryKey)) {
                stringBuilder.append(uriLockInline);
            } else if (dataField.equals(entity.expirationDateTime)) {
                stringBuilder.append(" NOW() + ");
                stringBuilder.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + dataField.getName());
            }
        }
        stringBuilder.append(")");
        stmts.add(stringBuilder.toString());
        stringBuilder.setLength(0);
    } else {
        // update existing record (prospective lock)
        stringBuilder.append("UPDATE ");
        stringBuilder.append(tableName);
        stringBuilder.append(" SET ");
        first = true;
        for (DataField f : entity.getFieldList()) {
            if (f == entity.primaryKey)
                continue;
            if (!first) {
                stringBuilder.append(",");
            }
            first = false;
            stringBuilder.append(K_BQ);
            stringBuilder.append(f.getName());
            stringBuilder.append(K_BQ);
            stringBuilder.append(" = ");
            if (f.equals(entity.creationDate) || f.equals(entity.lastUpdateDate)) {
                stringBuilder.append("NOW()");
            } else if (f.equals(entity.creatorUriUser) || f.equals(entity.lastUpdateUriUser)) {
                stringBuilder.append(uriUserInline);
            } else if (f.equals(entity.formId)) {
                stringBuilder.append(formIdInline);
            } else if (f.equals(entity.taskType)) {
                stringBuilder.append(taskTypeInline);
            } else if (f.equals(entity.primaryKey)) {
                stringBuilder.append(uriLockInline);
            } else if (f.equals(entity.expirationDateTime)) {
                stringBuilder.append(" NOW() + ");
                stringBuilder.append(lifetimeIntervalMilliseconds);
            } else {
                throw new IllegalStateException("unexpected case " + f.getName());
            }
        }
        stringBuilder.append(" WHERE ");
        stringBuilder.append(K_BQ);
        stringBuilder.append(entity.primaryKey.getName());
        stringBuilder.append(K_BQ);
        stringBuilder.append(" = ");
        stringBuilder.append(uriLockInline);
        stmts.add(stringBuilder.toString());
        stringBuilder.setLength(0);
    }
    // delete stale locks (don't care who's)
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ).append(" <= NOW()");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // delete prospective locks which are not the oldest for that resource and
    // task type
    dam.recordDeleteUsage(TaskLockTable.TABLE_NAME);
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(" AND ");
    stringBuilder.append(K_BQ).append(entity.expirationDateTime.getName()).append(K_BQ);
    stringBuilder.append(" > (SELECT MIN(t3.").append(K_BQ).append(entity.expirationDateTime.getName())
            .append(K_BQ);
    stringBuilder.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(")");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // delete our entry if it collides with another entry with exactly 
    // this time.
    stringBuilder.append("DELETE FROM ").append(tableName).append(" WHERE ");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND ");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(" AND ");
    stringBuilder.append(K_BQ).append(entity.primaryKey.getName()).append(K_BQ).append(" = ")
            .append(uriLockInline).append(" AND ");
    stringBuilder.append("1 < (SELECT COUNT(t3.").append(K_BQ).append(entity.expirationDateTime.getName())
            .append(K_BQ);
    stringBuilder.append(") FROM ").append(tableName).append(" AS t3 WHERE t3.");
    stringBuilder.append(K_BQ).append(entity.formId.getName()).append(K_BQ).append(" = ").append(formIdInline)
            .append(" AND t3.");
    stringBuilder.append(K_BQ).append(entity.taskType.getName()).append(K_BQ).append(" = ")
            .append(taskTypeInline).append(")");
    stmts.add(stringBuilder.toString());
    stringBuilder.setLength(0);
    // assert: only the lock that holds the resource for that task type appears
    // in the task lock table
    TaskLockTable relation;
    try {

        JdbcTemplate jdbc = datastore.getJdbcConnection();
        jdbc.execute(new ConnectionCallback<Object>() {

            @Override
            public Object doInConnection(Connection conn) throws SQLException, DataAccessException {
                boolean oldAutoCommitValue = conn.getAutoCommit();
                int oldTransactionValue = conn.getTransactionIsolation();
                try {
                    conn.setAutoCommit(false);
                    conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
                    Statement stmt = conn.createStatement();
                    for (String s : stmts) {
                        // for debugging: LogFactory.getLog(TaskLockImpl.class).info(s);
                        stmt.execute(s);
                    }
                    conn.commit();
                } catch (PSQLException e) {
                    e.printStackTrace();
                    conn.rollback();
                } catch (Exception e) {
                    e.printStackTrace();
                    conn.rollback();
                }
                conn.setTransactionIsolation(oldTransactionValue);
                conn.setAutoCommit(oldAutoCommitValue);
                return null;
            }

        });

        relation = TaskLockTable.assertRelation(datastore, user);
    } catch (Exception e) {
        throw new ODKTaskLockException(PERSISTENCE_LAYER_PROBLEM, e);
    }
    return (TaskLockTable) datastore.getEntity(relation, entity.getUri(), user);
}

---

From source file:org.jxstar.dao.pool.PooledConnection.java

/**
 * ?//from  w w  w  . j  a  va 2  s.c o m
 * ?queueConnect?????5
 * ?1???5???
 * ?validTest?validQuery?validIdle
 * 
 * @param DataSourceConfig   -- ???
 * @return
 */
private Connection getConnectionFromSelf(DataSourceConfig dsConfig) {
    Connection conn = null;
    DataSource ds = createSelfDataSource(dsConfig);
    boolean catchError = dsConfig.isCatchError();

    try {
        conn = ds.getConnection();
        //_log.showDebug("get connection is:" + conn);

        if (conn == null || conn.isClosed()) {
            String dsName = dsConfig.getDataSourceName();
            _log.showError("datasource [{0}] get connection is null!", dsName);
            return null;
        }

        int iTranLevel = getTranLevelConstant(dsConfig.getTranLevel());
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(iTranLevel);
    } catch (SQLException e) {
        if (catchError) {
            _log.showError(e);
        }
    }

    return conn;
}

---

From source file:backtype.storm.scheduler.adaptive.DataManager.java

public int checkTopology(String stormId) throws Exception {
    Connection connection = null;
    Statement statement = null;/*from w  ww . jav a  2s. c  o m*/
    ResultSet resultSet = null;
    int id = -1;
    logger.debug("Going to check topology " + stormId);
    try {
        connection = getConnection();
        connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
        connection.setAutoCommit(false);
        statement = connection.createStatement();

        String sql = "select id from topology where storm_id = '" + stormId + "'";
        logger.debug("SQL query: " + sql);
        resultSet = statement.executeQuery(sql);
        if (resultSet.next()) {
            id = resultSet.getInt(1);
            logger.debug("Topology found, id: " + id);
        } else {
            logger.debug("Topology not found, let's create it");
            resultSet.close();
            sql = "insert into topology(storm_id) values('" + stormId + "')";
            logger.debug("SQL script: " + sql);
            statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
            logger.debug("Retrieving generated id...");
            resultSet = statement.getGeneratedKeys();
            if (resultSet.next()) {
                id = resultSet.getInt(1);
                logger.debug("Ok, id: " + id);
            } else {
                throw new Exception("Cannot retrieve generated key");
            }
        }

        connection.commit();

    } catch (Exception e) {
        connection.rollback();
        logger.error("An error occurred checking a topology", e);
        throw e;
    } finally {
        if (resultSet != null)
            resultSet.close();
        if (statement != null)
            statement.close();
        if (connection != null)
            connection.close();
    }
    return id;
}

---

From source file:org.apache.hadoop.hive.metastore.MultiHdfsInfo.java

public String getDatabase(String name) throws NoSuchObjectException, MetaException {
    Database db = null;/*from   ww w  . j a v  a  2  s.c  o  m*/
    Connection con;
    name = name.toLowerCase();

    try {
        con = JDBCStore.getSegmentConnection(name);
    } catch (MetaStoreConnectException e1) {
        LOG.error("get database error, db=" + name + ", msg=" + e1.getMessage());
        throw new NoSuchObjectException(e1.getMessage());
    } catch (SQLException e1) {
        LOG.error("get database error, db=" + name + ", msg=" + e1.getMessage());
        throw new MetaException(e1.getMessage());
    }

    Statement stmt = null;

    try {
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

        stmt = con.createStatement();
        String sql = "SELECT name, hdfs_schema, description FROM DBS WHERE name='" + name + "'";

        ResultSet dbSet = stmt.executeQuery(sql);
        boolean isDBFind = false;

        while (dbSet.next()) {
            isDBFind = true;
            db = new Database();
            db.setName(dbSet.getString(1));
            db.setHdfsscheme(dbSet.getString(2));
            db.setDescription(dbSet.getString(3));
            break;
        }

        dbSet.close();

        if (!isDBFind) {
            LOG.error("get database error, db=" + name);
            throw new NoSuchObjectException("database " + name + " does not exist!");
        }
    } catch (SQLException sqlex) {
        sqlex.printStackTrace();
        throw new MetaException(sqlex.getMessage());
    } finally {
        JDBCStore.closeStatement(stmt);
        JDBCStore.closeConnection(con);
    }

    return db.getHdfsscheme();
}

---

From source file:org.cloudgraph.rdb.service.RDBGraphService.java

public SnapshotMap commit(DataGraph dataGraph, String username) {
    if (dataGraph == null)
        throw new IllegalArgumentException("expected non-null 'dataGraph' argument");
    if (username == null)
        throw new IllegalArgumentException("expected non-null 'username' argument");
    if (username.trim().length() == 0)
        throw new IllegalArgumentException("unexpected zero length 'username' argument");
    SnapshotMap snapshotMap = new SnapshotMap(new Timestamp((new Date()).getTime()));
    if (log.isDebugEnabled())
        log.debug("getting connection");
    Connection con = null;
    try {/*from  w ww .  j av a  2 s . co  m*/
        con = ProviderManager.instance().getConnection();
        if (con.getAutoCommit()) {
            if (log.isDebugEnabled())
                log.debug("turning off connection autocommit for graph commit");
            con.setAutoCommit(false);
        }
        // TODO: make transaction isolation configurable
        con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
        if (log.isDebugEnabled())
            log.debug(
                    "using transaction isolation level " + con.getTransactionIsolation() + " forgraph commit");
    } catch (SQLException e2) {
        if (con != null) {
            try {
                if (log.isDebugEnabled())
                    log.debug("closing connection");
                con.close();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        }
        throw new DataAccessException(e2);
    }
    DataGraphDispatcher dispatcher = new GraphDispatcher(snapshotMap, username, con);
    try {
        dispatcher.commit(dataGraph);
        con.commit();
        return snapshotMap;
    } catch (DataAccessException e) {
        if (log.isDebugEnabled())
            log.debug(e.getMessage(), e);
        try {
            con.rollback();
        } catch (SQLException e1) {
            log.error(e.getMessage(), e1);
        }
        throw e;
    } catch (Throwable t) {
        if (log.isDebugEnabled())
            log.debug(t.getMessage(), t);
        try {
            con.rollback();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
        }
        throw new DataAccessException(t);
    } finally {
        if (con != null)
            try {
                if (log.isDebugEnabled())
                    log.debug("closing connection");
                con.close();
            } catch (SQLException e) {
                log.error(e.getMessage(), e);
            }
        dispatcher.close();
    }
}

---

From source file:edu.umd.cs.submitServer.servlets.ReportTestOutcomes.java

/**
 * @param conn// w  ww  . j a  v a 2 s .  co m
 * @return
 * @throws SQLException
 */
private Connection switchToTransaction(Connection conn) throws SQLException {
    conn.close();
    // Begin a transaction.
    // We can set this to a low isolation level because
    // - We don't read anything
    // - The inserts/updates we perform should not affect
    // rows visible to any other transaction
    conn = getConnection();
    conn.setAutoCommit(false);
    conn.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
    return conn;
}

---

From source file:org.wso2.carbon.identity.account.suspension.notification.task.jdbc.JDBCNotificationReceiversRetrieval.java

private Connection getDBConnection(RealmConfiguration realmConfiguration)
        throws SQLException, UserStoreException {

    Connection dbConnection = null;
    DataSource dataSource = DatabaseUtil.createUserStoreDataSource(realmConfiguration);

    if (dataSource != null) {
        dbConnection = DatabaseUtil.getDBConnection(dataSource);
    }//from   ww w  . java  2  s.c o  m

    //if primary user store, DB connection can be same as realm data source.
    if (dbConnection == null && realmConfiguration.isPrimary()) {
        dbConnection = IdentityDatabaseUtil.getUserDBConnection();
    } else if (dbConnection == null) {
        throw new UserStoreException("Could not create a database connection to "
                + realmConfiguration.getUserStoreProperty(UserCoreConstants.RealmConfig.PROPERTY_DOMAIN_NAME));
    } else {
        // db connection is present
    }
    dbConnection.setAutoCommit(false);
    dbConnection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    return dbConnection;
}

---

From source file:com.amazon.carbonado.repo.jdbc.JDBCRepository.java

/**
 * Called by JDBCTransactionManager./*from  w  w  w.  j  a va2  s.c om*/
 */
Connection getConnectionForTxn(IsolationLevel level) throws FetchException {
    try {
        if (mOpenConnections == null) {
            throw new FetchException("Repository is closed");
        }

        // Get connection outside lock section since it may block.
        Connection con = mDataSource.getConnection();

        if (level == IsolationLevel.NONE) {
            con.setAutoCommit(true);
        } else {
            con.setAutoCommit(false);
            if (level != mDefaultIsolationLevel) {
                con.setTransactionIsolation(mapIsolationLevelToJdbc(level));
            }
        }

        mOpenConnectionsLock.lock();
        try {
            if (mOpenConnections == null) {
                con.close();
                throw new FetchException("Repository is closed");
            }
            mOpenConnections.put(con, null);
        } finally {
            mOpenConnectionsLock.unlock();
        }

        return con;
    } catch (Exception e) {
        throw toFetchException(e);
    }
}

---

From source file:org.jxstar.dao.pool.PooledConnection.java

/**
 * ????/* w  ww.j ava 2 s. co m*/
 * 
 * @param dsName   -- ????
 * @param iTranLevel      -- 
 * @return
 */
private Connection getConnectionFromContext(DataSourceConfig dsConfig) {
    Connection conn = null;
    String jndiName = dsConfig.getJndiName();
    boolean catchError = dsConfig.isCatchError();

    try {
        DataSource ds = (DataSource) _context.lookup(dsConfig.getJndiName());

        conn = ds.getConnection();
        //_log.showDebug("getConnection: " + conn);

        if (conn == null || conn.isClosed()) {
            String dsName = dsConfig.getDataSourceName();
            _log.showError("datasource [{0}] get connection is null!", dsName);
            return null;
        }

        int iTranLevel = getTranLevelConstant(dsConfig.getTranLevel());
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(iTranLevel);
    } catch (NamingException e) {
        if (catchError) {
            _log.showError("error get jndi name is: " + jndiName);
            _log.showError(e);
        }
    } catch (SQLException e) {
        if (catchError) {
            _log.showError("error get jndi name is: " + jndiName);
            _log.showError(e);
        }
    }

    return conn;
}

---

From source file:com.nextep.designer.dbgm.services.impl.DataService.java

private Connection getRepositoryConnection() throws SQLException {
    final IConnection repoConn = repositoryService.getRepositoryConnection();
    final Connection jdbcConn = connectionService.connect(repoConn);
    jdbcConn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
    return jdbcConn;
}

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• »