CODEWITHSUNDEEP
sql-serverjdbctransactions
Manrico Corazzi
Manrico Corazzi

Reputation: 11371

JDBC commit(): what happens behind the scenes?

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.

The database code always follows this scheme:

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

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 connection.commit() 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.

Or is it that as soon as I execute a prepared statement the DBMS locks the table?

EDIT: 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 executeStatement() acquires the lock immediately.

TIA

Upvotes: 3

Views: 11406

Answers (4)

Dhananjay
Dhananjay

Reputation: 3975

If you are interested in inside details, On connection.commit of a SQLServer JDBC implementation

Following command is issued 

IF @@TRANCOUNT > 0 COMMIT TRAN

@@TRANCOUNT = 0 -- no open transaction @@TRANCOUNT = 1 -- 1 open transaction @@TRANCOUNT =10 -- 10 open transaction

On setting autocommit to false, 

set implicit_transactions on

These are MS SQLServer specific commands.

Upvotes: 5

mcyalcin
mcyalcin

Reputation: 2082

What exactly goes on depends entirely on the driver implementation, so you need to check the documentation for the driver you are using to get a definitive answer.

setAutoCommit(false) does not necessarily begin the transaction on the database, and the statements do not necessarily execute on the database or even acquire the locks when you call the execute function in your code, as you surmised. That being said, as far as I know, shared (read) locks are ordinarily acquired when the statement is executed; update locks, when commit is called. You may be hitting a conversion deadlock (which happens when multiple statements's read locks on a given object are waiting to be converted to write locks), I'd check if your update statements have nested selects in them that may lead to such a lock.

Upvotes: 0

Shamit Verma
Shamit Verma

Reputation: 3827

connection.setAutoCommit(false);

Triggers "BEGIN TRAN" on DB Server and 

connection.commit();

Would trigger "COMMIT TRAN"

If you want to prevent locks between these two statements, set connection's isolation level to "Read Uncommited". You will have to ensure that it is acceptable in this scenario.

setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

Upvotes: 1

Jan Zyka
Jan Zyka

Reputation: 17888

According to this resource the transaction starts as soon as you call setAutocommit(false);

I think it might still be driver dependant but this will be typical. See also MSDN which says the same.

//Switch to manual transaction mode by setting
//autocommit to false. Note that this starts the first 
//manual transaction.
con.setAutoCommit(false);

Upvotes: 1

Related Questions