JDBC transactions and ResultSet(s) and SELECT statements

Viewed 3883

Because most tutorials I was able to find recently on JDBC & transactions usually cover only very simple cases like:

  1. Start transaction
  2. Issue several UPDATE / INSERT statements
  3. Commit transaction
  4. Rollback if any error occurs during transaction

I was wondering how does the correct template of JDBC transaction look like, especially in cases when there are SELECT statements involved.

As far as I've understood, the general template looks something like:

final int previousIsolationLevel = connection.getTransactionIsolation();
try {
    connection.setTransactionIsolation(desiredIsolationLevel);
    connection.setAutoCommit(false); // starting transaction

    // executing UPDATE / INSERT statements here using ps.executeUpdate(...)
    // executing SELECT statements and storing references to result sets like:
    // ResultSet rs1 = ps1.executeQuery(...);
    // ResultSet rs2 = ps2.executeQuery(...);
    // ...
    // ResultSet rsN = psN.executeQuery(...);

    connection.commit(); // committing transaction

    // reading data from rs1, ... , rsN AFTER commit like
    // while(rs1.next()) { ... }, while(rs2.next()) { ... }, ...
} catch (final Throwable ex) {
    connection.rollback(); // rolling back the transaction upon any exception
    // doing exception-handling stuff
} finally {
    connection.setAutoCommit(true); // setting auto-commit mode back to true
    connection.setTransactionIsolation(previousIsolationLevel); // setting transaction isolation to previous value
}

So the questions are:

  1. Is above mentioned template correct at least only for UPDATE / INSERT statements?
  2. Is it correct that if any SELECT statement occurs during transaction it is wrong to read it's ResultSet before commit?
  3. Are there any correlation between transaction isolation level and possibility to read from ResultSets before invoke to connection.commit() ? [this question extends 2nd one]
  4. Does the same rules apply to ResultSet returned from PreparedStatement.getGeneratedKeys() after INSERT statement was executed?

NOTE: I would like to find the most generic approach, but if it's very vendor specific, I'm using MySQL database. Though of course, I'm interested in and grateful for any help on this topic!

0 Answers
Related