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.
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.
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.
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!
Joined: Feb 15, 2010
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.
subject: commit - asynchronous or synchronous? (DB2)