jQuery in Action, 3rd edition
The moose likes JDBC and Relational Databases and the fly likes commit - asynchronous or synchronous? (DB2) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "commit - asynchronous or synchronous? (DB2)" Watch "commit - asynchronous or synchronous? (DB2)" New topic

commit - asynchronous or synchronous? (DB2)

Mark Clements

Joined: Feb 15, 2010
Posts: 5

We are observing behaviour with our applications that would imply that a call to commit is non-blocking and simply schedules a commit at the database rather than executing it and waiting for completion.


We have two command-line applications, one launched immediately after the other, both connecting to the same DB2 database.
App1 modifies data in the database, calls a commit on the connection and exits.
App2 runs and does not yet see the changes made by App1.

It is possible that we are misinterpreting what is happening, but this behaviour *could* be explained if the call to commit() by App1 is non-blocking.

The API docs for java.sql.Connection say:


Makes all changes made since the previous commit/rollback permanent and releases any database locks currently held by this Connection object. This method should be used only when auto-commit mode has been disabled.

This does not indicate if the call is blocking or non-blocking.

I've found little information about whether commit() is blocking or non-blocking in either generalized resources (here, the Sun docs) or specific IBM DB2 JDBC driver documentation (http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.apdv.java.doc/doc/tjvcmtrl.htm). I have found *some* information regarding Oracle:


which says that normally a commit is synchronous and that later versions of Oracle have an asynchronous commit option (it's years since I've worked with Oracle so apologies if I've misinterpreted this documentation).

I'd be most grateful for any pointers that anyone might have.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

Transactional databases tend follow ACID properties. If both have started a transaction at the same time for example, then neither will be able to see each other's work until they both commit. Is it possible App2 started its transaction before App1 committed?

There are a number of parameters you can add to the connection string to effect what processes can read. Take a look at this primer on isolation levels. There are times you do want processes to read uncomitted data, although I suspect there might be something else going on in your case.

[OCA 8 Book] [Blog]
Mark Clements

Joined: Feb 15, 2010
Posts: 5
Thanks very much for your help. I understand the issue of the different isolation levels and thanks for the pointer to the documentation, but I don't *think* that they come into it. The first transaction is very much complete by the time the second starts - App2 is not launched until App1 has completed and its process has exited. Thanks for the confirmation about the expected behaviour of commit - it's good to be able to all but eliminate a possibility. I agree that there must be something else going on that we haven't yet worked out. Thanks again!
Mark Clements

Joined: Feb 15, 2010
Posts: 5
It turns out that the issue was caused by not having deployed the correct program versions, and this is why we were seeing unusual behaviour - nothing to do with the database and/or JDBC. Thanks again.
I agree. Here's the link: http://aspose.com/file-tools
subject: commit - asynchronous or synchronous? (DB2)
It's not a secret anymore!