db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <mse...@segel.com>
Subject Re: I need some advice to choose database for an upcomming job
Date Wed, 09 Nov 2005 16:17:02 GMT
On Wednesday 09 November 2005 08:26, Lance J. Andersen wrote:
> Michael Segel wrote:
> >On Tuesday 08 November 2005 16:39, Lance J. Andersen wrote:
> >>Daniel John Debrunner wrote:
> >>>Lance J. Andersen wrote:
> >>>>>>Note that executing a statement on *another* statement object
in the
> >>>>>>same connection no longer closes a result set,
> >>>>
> >>>>This has never been the intent in JDBC since its inception,
> >>>
> >>>Well, it sure had a funny way of showing it was not the intent :-)
> >>>
> >>>JDBC 1
> >>>
> >>>"New JDBC connections are initially in “auto-commit” mode. This means
> >>>that each statement is executed as a separate transaction at the
> >>>database. In order to execute several statements within a single
> >>>transaction, you must first disable auto-commit by calling
> >>>Connection.setAutoCommit(false)."
> >>
> >>I assume you are refering to the JDBC 102 spec , i am aware of this
> >>verbage.
> >>
> >>The above wording does not specify what happens to the Statement that
> >>was active.  Is it commited or rolled back?  I am sure your milage
> >>varies as it does if you do a Connection.close() and there is an active
> >>transaction (The SQL standard differs from the reality of vendors.  Some
> >>commit, some rollback some just give an Error and expect the user to
> >>address it as the standard suggests)
> >
> >A connection.close() is a method on the connection object. Since the
> > result sets and statements are all relative to the connection object,
> > clearly there will be problems and this is not the intention as per the
> > earlier spec.
> >
> >I think the bigger question is how do you account for transactions within
> > a connection?  That appears to be the issue.
> >If all of your SQL are atomic, then you don't have a problem.  ;-)
> The SQL Standard indicates that if you disconnect while a transaction is
> active, that an error will be raised.  Nothing more, nothing less.
No argument there. And I think that you've missed the point I was trying to 

Its not how the database vendor handles transactions, but rather how JDBC 
handles them.

If you look at the API spec (http://java.sun.com/j2se/1.5.0/docs/api/) and 
review the java.sql Connection Interface, you will see that at the Connection 
object level, you control the transactions for the entire connection object.

If you look at the methods, clearly there is only one single transaction 
context per Connection object. This only becomes an issue when your 
statements are no longer atomic in nature... ;-)

> some vendors Commit, some rollback, some give the error and expect the
> end user to do the right thing.
> Unfortunately vendors are not eager to change their semantics once their
> technologies have been in the field.  So unfortunately the specs (any of
> them will not
> provide a silver bullet from poor design of an application)

Ahh, so true. A barrier to the adoption of new technology because their is an 
economic disincentive... (Nothing new there, just look at the phone companies 
in the US.)

But this is really irrelevant. Unless you're talking about changing the JDBC 

You have really two ways to look at this.

1) The JDBC spec is fine, and the Connection object   should manage the 

2) You break out the transactions as a separate class object and then 
associate the statement objects to them. And "register" the transaction with 
a Connection Object. (read:  Connection Object <- 1:n Transaction Contexts 
<-1:n Statement Objects)

Now in the first choice, everything stays the same and you guide developers to 
create a separate database connection for each transaction context that you 
want to run in parallel (multi-threaded).  Now if you're going to ensure that 
all of your statements are atomic, You can still do what you're doing, but 
you run the risk of getting screwed when someone wants to implement 

With respect to the second option, As long as you allow for a statement to 
only be registered with a single transaction, and you don't allow for nesting 
of transactions, you should be ok. 

Not to say that this is trivial, its not rocket science either.
You would have to create the structures and methods to manage multiple 
transactionContexts, and also within the transactionContext the ability to 
instantiate and manage multiple Statement objects. Note that this is done so 
that you can control the children elements and avoid the potential of a 
memory leak. (Its been a while, but during GC, does Java clean up objects 
with circular references?) 

But I digress. The point:	
	If done properly, you will actually allow a lot more freedom in design.

But hey, what do I know?

I've only had one cup of coffee this morning, haven't thought the design 
elements all out.. and of course drinking that last batch of Obnoxio the 
Clown's (OTC to you Informix types...) bath tub gin really hit me hard. I 
guess that those rumors he's be running a meth lab out of the same bathroom 
and tub might be true. ;-)


Michael Segel

View raw message