title: JDBC transactions and ResultSet(s) and SELECT statements
body: <p>Because most tutorials I was able to find recently on <strong>JDBC &amp; transactions</strong> usually cover only very simple cases like: </p>
  
  <ol>
  <li><strong>Start</strong> transaction</li>
  <li>Issue <strong>several UPDATE / INSERT</strong> statements</li>
  <li><strong>Commit</strong> transaction</li>
  <li><strong>Rollback</strong> if any error occurs during transaction</li>
  </ol>
  
  <p>I was wondering how does the correct <em>template</em> of JDBC transaction look like, especially in cases when there are <strong>SELECT</strong> statements involved.</p>
  
  <p>As far as I've understood, the general template looks something like:</p>
  
  <pre><code>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
  }
  </code></pre>
  
  <p>So the questions are:</p>
  
  <ol>
  <li>Is above mentioned template correct at least only for <strong>UPDATE / INSERT statements</strong>?</li>
  <li>Is it correct that if any <strong>SELECT</strong> statement occurs during transaction it is <strong>wrong</strong> to read it's <strong>ResultSet</strong> before commit? </li>
  <li>Are there any correlation between <strong>transaction isolation level</strong> and possibility to read from <strong>ResultSets</strong> before invoke to <strong>connection.commit()</strong> ? <em>[this question extends 2nd one]</em></li>
  <li>Does the same rules apply to <strong>ResultSet</strong> returned from <a href="http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys%28%29" rel="nofollow">PreparedStatement.getGeneratedKeys()</a> after <strong>INSERT statement</strong> was executed?</li>
  </ol>
  
  <p><strong>NOTE</strong>: I would like to find the most generic approach, but if it's very vendor specific, I'm using <strong>MySQL</strong> database. Though of course, I'm interested in and grateful for any help on this topic!</p>