db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Philip Wilder <0505...@acadiau.ca>
Subject Re: JDBC auto-commit semantics challenge
Date Fri, 24 Jun 2005 13:36:09 GMT
I'm submitted a few changes to the last document that might aid 
understanding, particularly in the advanced section of the document. In 
addition, I've switched from Word docs and PDFs to straight text to make 
is easier to add inline comments.



The following document is a proposed outline for Derby commit 
functionality. It is hoped that this document can eliminate the 
ambiguity of the JDBC documentation and centralize the information 
needed to provide an implementation that matches JDBC expectations. It 
was not possible in all cases to make these decisions based solely upon 
the supporting documentation, so there may be instances where this 
document makes reference to external sources or attempts to provides a 
reasonable interpretation based on the available data.


An Auto-commit occurs when one of the following occurs:
1. When any Statement Completes
2. When any Statement Executes. This will implicitly close any 
ResultSets associated with this Statement.
3. When a call to connection.setAutoCommit() is made during a 
transaction that changes the value autoCommit.

A Statement completes when:
• DDL: After execution.
o Simple Cases
- Delete/Insert/Update: After execution.
- Query:
• FORWARD_ONLY: After the final row of the ResultSet has been retrieved 
or the ResultSet has been closed.
• SCROLLABLE: After the ResultSet has been closed.
o Advanced Case
- Multiple Results (currently only applicable to CallableStatements): 
All results have been retrieved. Results include
• ResultSets: A ResultSet has been retrieved when getMoreResults has 
been called and the query results retrieved as described in simple cases.
• Update counts: An Update count is returned for ddl, inserts, updates 
and deletes. Update counts have been retrieved after getUpdateCount has 
been called or getMoreResults() has been called to get the next result 
for the statement.
- Output parameters: Output parameters are associated with 
CallableStatements. Output parameters have been retrieved when an 
appropriate CallableStatement getter method has been called for each 
output parameter.

Since DatabaseMetaData does not make use of any Statement objects 
accessible to the user ResultSets that come from DatabaseMetaData 
statements should be committed only when no other Statements are 
currently open.

Related Questions

• What happens to other statements when the auto-commit occurs?
o Commits are Connection wide so a commit from one statement will affect 
all statements.
• Should special consideration be given the Distributed transactions?
o Auto-committing is not supported for distributed transactions.
• What is the proper commit action of a call to executeBatch()?
o The JDBC specification does not specify when an auto-commit should 
occur in an executeBatch Statement.




Submits a batch of commands to the database for execution and if all 
commands execute successfully, returns an array of update counts. The 
int elements of the array that is returned are ordered to correspond to 
the commands in the batch, which are ordered according to the order in 
which they were added to the batch. The elements in the array returned 
by the method executeBatch may be one of the following:
A number greater than or equal to zero -- indicates that the command was 
processed successfully and is an update count giving the number of rows 
in the database that were affected by the command's execution
A value of SUCCESS_NO_INFO -- indicates that the command was processed 
successfully but that the number of rows affected is unknown

If one of the commands in a batch update fails to execute properly, this 
method throws a BatchUpdateException, and a JDBC driver may or may not 
continue to process the remaining commands in the batch. However, the 
driver's behavior must be consistent with a particular DBMS, either 
always continuing to process commands or never continuing to process 
commands. If the driver continues processing after a failure, the array 
returned by the method BatchUpdateException.getUpdateCounts will contain 
as many elements as there are commands in the batch, and at least one of 
the elements will be the following:

A value of EXECUTE_FAILED -- indicates that the command failed to 
execute successfully and occurs only if a driver continues to process 
commands after a command fails

A driver is not required to implement this method. The possible 
implementations and return values have been modified in the Java 2 SDK, 
Standard Edition, version 1.3 to accommodate the option of continuing to 
proccess commands in a batch update after a BatchUpdateException obejct 
has been thrown.

an array of update counts containing one element for each command in the 
batch. The elements of the array are ordered according to the order in 
which commands were added to the batch.
SQLException - if a database access error occurs or the driver does not 
support batch statements. Throws BatchUpdateException (a subclass of 
SQLException) if one of the commands sent to the database fails to 
execute properly or attempts to return a result set.


Sets this connection's auto-commit mode to the given state. If a 
connection is in auto-commit mode, then all its SQL statements will be 
executed and committed as individual transactions. Otherwise, its SQL 
statements are grouped into transactions that are terminated by a call 
to either the method commit or the method rollback. By default, new 
connections are in auto-commit mode.

The commit occurs when the statement completes or the next execute 
occurs, whichever comes first. In the case of statements returning a 
ResultSet object, the statement completes when the last row of the 
ResultSet object has been retrieved or the ResultSet object has been 
closed. In advanced cases, a single statement may return multiple 
results as well as output parameter values. In these cases, the commit 
occurs when all results and output parameter values have been retrieved.

NOTE: If this method is called during a transaction, the transaction is 


Moves to this Statement object's next result, deals with any current 
ResultSet object(s) according to the instructions specified by the given 
flag, and returns true if the next result is a ResultSet object.

There are no more results when the following is true:

// stmt is a Statement object
((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

current - one of the following Statement constants indicating what 
should happen to current ResultSet objects obtained using the method 
getResultSet: Statement.CLOSE_CURRENT_RESULT, 

true if the next result is a ResultSet object; false if it is an update 
count or there are no more results
JDBC API Tutorial and Reference, Second Edition
Universal Data Access of the Java 2 Platform

Page 347 setAutoCommit

The commit occurs when the statement completes or the next execute 
occurs, whichever comes first. In the case of statements returning a 
ResultSet object, the statement completes when the last row of a 
non-scrollable resultset has been retrieved or the ResultSet object has 
been closed.
Derby Comment: Currently Embedded Derby considers the Statement complete 
when the last row has been returned for both scrollable and 
non-scrollable ResultSets.

Page 819, Section 40.1.3 Statement Completion

A Statement is considered Complete when it has been executed and all its 
results have been returned. For the method executeQuery, which returns 
one result set the statement is completed when all the rows of the 
ResultSet object have been retrieved. For the method executeUpdate, a 
statement is completed when it is executed. In rare cases where the 
method execute is called, however, a statement is not complete until all 
the result sets or update counts it generated have been retrieved.

Page 996 Glossary Transaction

A sequence of SQL/JDBC calls that constitute an atomic unit of work: 
Either all of the commands in a transaction are committed as a unit, or 
all of the commands are rolled back as a unit. Transactions provide ACID 
properties: atomicity, consistency, integrity of data and durability of 
database changes. See commit and rollback. A transaction in which 
commands are sent to more then one DBMS server is a distributed 

Philip Wilder wrote:

>> Hello all,
>> In case you haven't been following the DERBY-213 chat transcripts for 
>> about a week now Kathey Marsden and I have been working to establish 
>> a concrete outline for the auto-commit behavior we wish to see 
>> implemented with Derby. Many of the problems we have been having with 
>> DERBY-213 have stemmed from ambiguous interpretations of the JDBC 
>> spec, so we felt it would be beneficial to both us and the dev 
>> community at large if we took the time to get it right.
>> At the moment we are on the 4th draft of the document and it has 
>> reached a stage that is satisfactory to both Kathey and I. As such, 
>> we felt that it was time to get your input. Hopefully if enough 
>> people can agree upon a single definition we can change this document 
>> to match the general consensus and incorporate it into the
>> http://incubator.apache.org/derby/papers/JDBCImplementation.html 
>> paper as per Dan's suggestion.
>> Anyway, I know many of you are busy preparing for JavaOne but any 
>> time you can take to challenge any of our semantic assertions or just 
>> correct my grammar would be appreciated.
>> Philip
Two notes regarding this issue:

a) Kathey and I are currently scheduled for a IRC meeting on 
irc.freenode.net server, #derby channel at June 28, 5:00 a.m. PST. If 
you have an interest in this issue but are unable to make it to this 
time we may be able to make alternate arrangements.

b) Kathey has expressed an interest in having a wiki set up for this 
issue and I can see the benefit of one. Does anyone out there know what 
channels we need to go through to get our own wiki page?


View raw message