title: JDBC commit(): what happens behind the scenes?
body: <p>I'm helping with a deadlock hunt. The environment: Tomcat 5.5, Java 5, Microsoft SQL Server 2008, jTDS (replacing an old driver). We have a legacy connection pool.</p>
  
  <p>The database code always follows this scheme:</p>
  
  <pre><code>connection = connectionPool.getConnection(); // 1
  boolean previousAutoCommitStatus = connection.getAutoCommit(); // 2
  connection.setAutoCommit(false); // 3
  
  // ... use the connection ...
  // execute prepared statement 4
  // execute prepared statement 5
  // execute prepared statement 6
  
  connection.commit(); // 7
  connection.setAutoCommit(previousAutoCommitStatus); // 8
  connectionPool.releaseConnection(connection); // 9
  </code></pre>
  
  <p>While we hunt the bug (pardon: the software defect) I was wondering: how does the driver work? My guess: whatever I do between (3) and (7) is queued by the driver/the DBMS. Only when I <code>connection.commit()</code> the DBMS begins a new transaction, acquires every lock the operations need (I hope that it is smart enough to lock the smaller possible set of objects), executes the statements and releases the lock, thus closing the transaction.</p>
  
  <p>Or is it that as soon as I execute a prepared statement the DBMS locks the table?</p>
  
  <p><strong>EDIT</strong>: What I want to understand is if "commit()" translates in a set of SQL statements beginning with "begin trans/lock table" and ending in "commit/unlock table" or if any Java <code>executeStatement()</code> acquires the lock immediately.</p>
  
  <p>TIA</p>