You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Legolas Woodland <le...@gmail.com> on 2005/10/29 16:29:38 UTC
I need some advice to choose database for an upcomming job
Hi
Thank you for reading my post
I should design and implement an application which is going to run on tomcat
5.5 and Database can be one of derby ,sqlServer , Mysql.
Problem is that this application should be able to have about 15
-20Transaction in 1 second .They have no extra power servers , just an
ordinary hosting plan.
my question is : which of this database is better to choose ?
i thought that derby could be better because it can be embeded into my
application .
can derby carry out 15-20 transacion in 1 secod ?is it feasible with using
DAO pattern and plain JDBC ,or it need some other requirement ?
I have Struts and mysql experience I learned derby but i did not use it yet.
before they ask me about implementing this project they used PHP+Mysql and
it seems that that application Hangs on +10 hits per second
each hit had some Insert one update and 2 select at least.
Thank you
Re: I need some advice to choose database for an upcomming job
Posted by Øystein Grøvlen <Oy...@Sun.COM>.
>>>>> "BP" == Bryan Pendleton <bp...@amberpoint.com> writes:
>> A "Guidelines" section starts on slide 19. Slide 24 lists 100-500
>> updates per second -- but, of course, your actual performance will
>> depend on the complexity of your transactions.
BP> Is there a simple way that I can observe what actual performance
BP> I'm getting? That is, does Derby keep its own monitoring statistics
BP> about the activity that is occuring, and, if so, how do I retrieve
BP> those statistics and view them?
BP> I see that there is SYSCS_GET_RUNTIME_STATISTICS, but that seems
BP> to be mostly about execution of a single query; how do I see
BP> information like:
BP> - transactions per second
BP> - IOs per second (read and write)
BP> - active users
BP> - memory usage
BP> etc.
I agree that this would be very useful.
--
Øystein
Re: I need some advice to choose database for an upcomming job
Posted by Bryan Pendleton <bp...@amberpoint.com>.
> A "Guidelines" section starts on slide 19. Slide 24 lists 100-500
> updates per second -- but, of course, your actual performance will
> depend on the complexity of your transactions.
Is there a simple way that I can observe what actual performance
I'm getting? That is, does Derby keep its own monitoring statistics
about the activity that is occuring, and, if so, how do I retrieve
those statistics and view them?
I see that there is SYSCS_GET_RUNTIME_STATISTICS, but that seems
to be mostly about execution of a single query; how do I see
information like:
- transactions per second
- IOs per second (read and write)
- active users
- memory usage
etc.
thanks,
bryan
Re: I need some advice to choose database for an upcomming job
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Jean T. Anderson wrote:
> ...
> Also you might be interested in an article Stan Bradbury contributed
> yesterday that shows a way to embed Derby in Tomcat 5.5. You can find it
> here:
>
> http://db.apache.org/derby/integrate/DerbyTomcat5512JPetStor.html
> ...
I just spotted that the Links Section points to the Geronimo download
instead of the Tomcat download (this is a companion to papers Stan also
contributed for Geronimo and WebSphere). The fix will be on the web site
in an hour or so. In the meantime, the link for the Tomcat download at
the top of the paper in the "Required Software / Downloads" section is
correct.
Also I just noticed that the article says to use Derby 10.0.2.1, but it
should work just fine with 10.1 at
http://db.apache.org/derby/releases/release-10.1.1.0.cgi . Right, Stan?
thanks,
-jean
Re: I need some advice to choose database for an upcomming job
Posted by Oy...@Sun.COM.
Jean T. Anderson wrote:
> Oyvind.Bakksjo@Sun.COM wrote:
>
>> ...
>> I would like to suggest reading my (short but excellent ;o) blog
>> entry, where I describe how one can do lifecycle management in a
>> standard (container-agnostic) way, which neither requires access to
>> the Tomcat configuration nor stopping/starting the server.
>>
>> http://weblogs.java.net/blog/bakksjo/archive/2005/09/embedding_the_a_1.html
>>
>>
>
> Is this link to your blog entry persistent, Oyvind? --if I add it to the
> Derby web site, will it still be good 6 months from now?
Yes, I think blog entries on java.net should be persistent.
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: I need some advice to choose database for an upcomming job
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Oyvind.Bakksjo@Sun.COM wrote:
> ...
> I would like to suggest reading my (short but excellent ;o) blog entry,
> where I describe how one can do lifecycle management in a standard
> (container-agnostic) way, which neither requires access to the Tomcat
> configuration nor stopping/starting the server.
>
> http://weblogs.java.net/blog/bakksjo/archive/2005/09/embedding_the_a_1.html
>
Is this link to your blog entry persistent, Oyvind? --if I add it to the
Derby web site, will it still be good 6 months from now?
thanks,
-jean
Re: I need some advice to choose database for an upcomming job
Posted by Michael Segel <ms...@segel.com>.
On Monday 07 November 2005 11:43, Oyvind.Bakksjo@sun.com wrote:
This is a good question....
According to "JDBC Database Access with Java" ISBN 020130995-5 (my copy is
from 1998...)
In Apendix A "For Driver Writers" A.1.6 Support Multithreading
"All operations on java.sql objects are required to be thread safe. " ...
[Some fun reading]
"For example, two Statement objects on the same connection can be executed
concurrently, and their ResultSets can be processed concurrently (from the
perspective of the developer). ..."
So your code should work in "all" JDBC environments.
HTH
-G
> Daniel John Debrunner wrote:
> > Oyvind.Bakksjo@Sun.COM wrote:
> >>Note B: If you're running with autocommit OFF, you should definately not
> >>use the same connection object in multiple simultaneous requests (either
> >>use synchronization or create multiple connections).
> >
> > This is true even with automcommit on. Multiple threads using the same
> > connection with ResultSets will mess with each other, as per the JDBC
> > sprc. An executeQuery by one thread will close any ResultSet any other
> > thread is processing.
>
> Could you elaborate on this?
>
> I created a small test program which Prepares two statements in separate
> threads, but uses the same connection object. I execute statement A and
> read a few rows from the resultset in thread 1, execute statement B and
> read a few rows from it in thread 2, do a little interleaved reading,
> then read the rest of the rows from both resultsets in their respective
> threads. Got no exception, indicating that no result set was closed.
>
> I ran this in autocommit mode in both embedded and client/server mode.
>
> Code excerpt below, in case my explanation of the test program was unclear:
>
> t1.prepareStatement();
> t2.prepareStatement();
> t1.executeQuery();
> t1.showRow();
> t1.showRow();
> t1.showRow();
> t2.executeQuery();
> t2.showRow();
> t2.showRow();
> t2.showRow();
> t1.showRow();
> t2.showRow();
> t1.showAll();
> t2.showAll();
>
> I have seen some differing behaviour with respect to this in
> client/server and embedded mode before (that's why I made the test), but
> I couldn't reproduce any issue now.
--
Michael Segel
Principal
MSCC
Re: I need some advice to choose database for an upcomming job
Posted by Oy...@Sun.COM.
Daniel John Debrunner wrote:
> Oyvind.Bakksjo@Sun.COM wrote:
>
>
>>Note A: You may want to create and use more than one connection to
>>increase performance if you have many simultaneous requests.
>
>
> I would not recommend a single connection for any simultaneous requests.
>
>
>>Note B: If you're running with autocommit OFF, you should definately not
>>use the same connection object in multiple simultaneous requests (either
>>use synchronization or create multiple connections).
>
>
> This is true even with automcommit on. Multiple threads using the same
> connection with ResultSets will mess with each other, as per the JDBC
> sprc. An executeQuery by one thread will close any ResultSet any other
> thread is processing.
>
> If you are running Derby embedded then the cost of creating a connection
> per request will be much lower than traditional client server databases,
> and most likely will be a small fraction of the cost of the query or update.
That's true, as long as the database is not booted & shut down with each
request. That was the case in the "prototype integration" described in
the referred article.
As long as the database is booted & shut down only in the context
listener class, it should be fine to create new embedded connections
with each request.
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: I need some advice to choose database for an upcomming job
Posted by Oy...@Sun.COM.
Oyvind.Bakksjo@Sun.COM wrote:
> Daniel John Debrunner wrote:
>
>> My thinking is that the executeQuery() will cause a commit which will
>> cause the open held ResultSet used by the other thread to move off the
>> row, as required by the SQL standard. Thus before any getXXX() call can
>> be made, the held cursor needs to be re-positioned using next(). I think
>> you have said before that Derby doesn't act this way, though looking at
>> the embedded code it should.
>
>
> So I tried the following:
>
> conn.setAutoCommit(true);
> Statement st1 = conn.createStatement();
> try {
> st1.execute("drop table t1");
> } catch (SQLException e) {
> // Ignore
> }
> st1.execute("create table t1 (a int)");
> st1.execute("insert into t1
> values(0),(1),(2),(3),(4),(5),(6)");
>
> PreparedStatement ps1 = conn.prepareStatement("select * from
> SYS.systables",
>
> ResultSet.TYPE_FORWARD_ONLY,
>
> ResultSet.CONCUR_READ_ONLY,
>
> ResultSet.HOLD_CURSORS_OVER_COMMIT);
> PreparedStatement ps2 = conn.prepareStatement("select * from
> t1",
>
> ResultSet.TYPE_FORWARD_ONLY,
>
> ResultSet.CONCUR_READ_ONLY,
>
> ResultSet.HOLD_CURSORS_OVER_COMMIT);
> ResultSet rs1 = ps1.executeQuery();
> ResultSetMetaData md1 = rs1.getMetaData();
> rs1.next();
> show(rs1, md1);
> rs1.next();
> ResultSet rs2 = ps2.executeQuery();
> ResultSetMetaData md2 = rs2.getMetaData();
> show(rs1, md1);
> rs2.next();
> show(rs2, md2);
> rs2.next();
> show(rs2, md2);
> rs1.next();
> show(rs1, md1);
> rs2.next();
> show(rs2, md2);
>
>
> (The show method calls ResultSet.getString on all columns.)
>
> As you can see, the second executeQuery() call is between rs1.next() and
> show(rs1, md1). There's no exception thrown - no need to reposition, it
> seems. I changed the prepareStatement for ps1 to use
> CLOSE_CURSORS_AT_COMMIT, and then I got an exception about the result
> set being closed, so there's definitely a commit made at the second
> executeQuery.
I think I am finally beginning to grasp what is going on here. :)
-----------------------
JDBC 3.0 - Section 10.1.
A commit occurs when a statement is complete in auto-commit mode.
For Select statements, the statement is complete when the associated
result set is closed. The result set is closed as soon as one of the
following occurs:
...
another Statement object is executed on the same connection
------------------------
Reading this from the bottom and up, what happens when running with
CLOSE_CURSORS_AT_COMMIT is that executing the other statement causes the
first result set to be closed, causing the statement to complete,
causing the transaction to commit.
Now, if we have HOLD_CURSORS_OVER_COMMIT, we don't want the result set
to be closed after the commit. But since closing the result set is what
triggers the commit, it would mean that the result set would first have
to be closed (in order to trigger the commit) and then, after the
commit, to not be closed after all! What I suspect is that when we have
specified HOLD_CURSORS_OVER_COMMIT, the second execute does not close
the result set in the first place (and here the spec quote above is
vague, it does not state anything about holdability), thus never causing
a commit.
Quite funny, then, that whether the second execute triggers a commit
depends on the holdability of other result sets.
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: I need some advice to choose database for an upcomming job
Posted by Oy...@Sun.COM.
Daniel John Debrunner wrote:
> Oyvind.Bakksjo@Sun.COM wrote:
>
>
>>Daniel John Debrunner wrote:
>
>
>>>In addition I guess you showRow() does a next() and then
>>>the rs.gerXXX()? I think if you called t2.executeQuery() between a
>>>next() and the rs.getXXX() calls on the other thread, I think you will
>>>see problems.
>>
>>
>>I tried that, but that did not change any behaviour. Besides, why would
>>it? It doesn't seem logical to me why you would get an exception exactly
>>there, if the resultset isn't closed and you don't get an exception on
>>the subsequent next() and rs.getXXX() calls after the second execute.
>
>
> My thinking is that the executeQuery() will cause a commit which will
> cause the open held ResultSet used by the other thread to move off the
> row, as required by the SQL standard. Thus before any getXXX() call can
> be made, the held cursor needs to be re-positioned using next(). I think
> you have said before that Derby doesn't act this way, though looking at
> the embedded code it should.
So I tried the following:
conn.setAutoCommit(true);
Statement st1 = conn.createStatement();
try {
st1.execute("drop table t1");
} catch (SQLException e) {
// Ignore
}
st1.execute("create table t1 (a int)");
st1.execute("insert into t1
values(0),(1),(2),(3),(4),(5),(6)");
PreparedStatement ps1 = conn.prepareStatement("select *
from SYS.systables",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY,
ResultSet.HOLD_CURSORS_OVER_COMMIT);
PreparedStatement ps2 = conn.prepareStatement("select *
from t1",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY,
ResultSet.HOLD_CURSORS_OVER_COMMIT);
ResultSet rs1 = ps1.executeQuery();
ResultSetMetaData md1 = rs1.getMetaData();
rs1.next();
show(rs1, md1);
rs1.next();
ResultSet rs2 = ps2.executeQuery();
ResultSetMetaData md2 = rs2.getMetaData();
show(rs1, md1);
rs2.next();
show(rs2, md2);
rs2.next();
show(rs2, md2);
rs1.next();
show(rs1, md1);
rs2.next();
show(rs2, md2);
(The show method calls ResultSet.getString on all columns.)
As you can see, the second executeQuery() call is between rs1.next() and
show(rs1, md1). There's no exception thrown - no need to reposition, it
seems. I changed the prepareStatement for ps1 to use
CLOSE_CURSORS_AT_COMMIT, and then I got an exception about the result
set being closed, so there's definitely a commit made at the second
executeQuery.
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: I need some advice to choose database for an upcomming job
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Oyvind.Bakksjo@Sun.COM wrote:
> Daniel John Debrunner wrote:
>> In addition I guess you showRow() does a next() and then
>> the rs.gerXXX()? I think if you called t2.executeQuery() between a
>> next() and the rs.getXXX() calls on the other thread, I think you will
>> see problems.
>
>
> I tried that, but that did not change any behaviour. Besides, why would
> it? It doesn't seem logical to me why you would get an exception exactly
> there, if the resultset isn't closed and you don't get an exception on
> the subsequent next() and rs.getXXX() calls after the second execute.
My thinking is that the executeQuery() will cause a commit which will
cause the open held ResultSet used by the other thread to move off the
row, as required by the SQL standard. Thus before any getXXX() call can
be made, the held cursor needs to be re-positioned using next(). I think
you have said before that Derby doesn't act this way, though looking at
the embedded code it should.
>> Thus sharing connections across threads is just problematic unless the
>> application performs synchronization and/or has very good knowledge of
>> what others threads are doing at all times. Any application will just be
>> less error prone if it uses separate connections for separate threads,
>> isn't one of the reasons to use a relational database to not have to
>> worry about data synchronization issues? This of course is not specific
>> to Derby, the JDBC spec specifies this behavviour.
>
>
> I agree. Just to be clear, I am not arguing that anyone should code
> applications that way, I'm just trying to figure out exactly how Derby
> behaves.
Always good :-)
>
> Although sharing connections between unrelated threads which perform
> different tasks is not a good idea, I can imagine there are cases where
> one would benefit from having multiple open resultsets on a connection
> with autocommit off. This should be allowed, right? Also, Derby should
> be agnostic to whether these result sets are processed in different
> threads.
Yes, it's allowed, and Derby does not care what thread is processing a
ResultSet. It's just the JDBC spec mandates behaviour which means
applications which have multiple threads using a single connection need
to be co-ordinated in some way. If there is no co-ordination then any
commit or rollback by one thread can/will affect the open objects used
by the other thread(s).
Dan.
Re: I need some advice to choose database for an upcomming job
Posted by Oy...@Sun.COM.
Daniel John Debrunner wrote:
> Oyvind.Bakksjo@Sun.COM wrote:
>
>>Daniel John Debrunner wrote:
>>
>>
>>>Oyvind.Bakksjo@Sun.COM wrote:
>>>
>>>
>>>>Note B: If you're running with autocommit OFF, you should definately not
>>>>use the same connection object in multiple simultaneous requests (either
>>>>use synchronization or create multiple connections).
>>>
>>>
>>>
>>>This is true even with automcommit on. Multiple threads using the same
>>>connection with ResultSets will mess with each other, as per the JDBC
>>>sprc. An executeQuery by one thread will close any ResultSet any other
>>>thread is processing.
>>
>>
>>Could you elaborate on this?
>>
>>I created a small test program which Prepares two statements in separate
>>threads, but uses the same connection object. I execute statement A and
>>read a few rows from the resultset in thread 1, execute statement B and
>>read a few rows from it in thread 2, do a little interleaved reading,
>>then read the rest of the rows from both resultsets in their respective
>>threads. Got no exception, indicating that no result set was closed.
>>
>>I ran this in autocommit mode in both embedded and client/server mode.
>>
>>Code excerpt below, in case my explanation of the test program was unclear:
>>
>> t1.prepareStatement();
>> t2.prepareStatement();
>> t1.executeQuery();
>> t1.showRow();
>> t1.showRow();
>> t1.showRow();
>> t2.executeQuery();
>> t2.showRow();
>> t2.showRow();
>> t2.showRow();
>> t1.showRow();
>> t2.showRow();
>> t1.showAll();
>> t2.showAll();
>>
>>I have seen some differing behaviour with respect to this in
>>client/server and embedded mode before (that's why I made the test), but
>>I couldn't reproduce any issue now.
>
>
> -----------------------
> JDBC 3.0 - Section 10.1.
>
> A commit occurs when a statement is complete in auto-commit mode.
> For Select statements, the statement is complete when the associated
> result set is closed. The result set is closed as soon as one of the
> following occurs:
> ...
> another Statement object is executed on the same connection
> ------------------------
>
> So in your example the call to t2.executeQuery() should cause a commit
> on the connection which will close the ResultSet for t1.
>
> Your code probably has ResultSets which are held
> HOLD_CURSORS_OVER_COMMIT (which is the default) and thus are not closed
> by the commit.
You're right, after changing this to CLOSE_CURSORS_AT_COMMIT I got
exceptions.
> In addition I guess you showRow() does a next() and then
> the rs.gerXXX()? I think if you called t2.executeQuery() between a
> next() and the rs.getXXX() calls on the other thread, I think you will
> see problems.
I tried that, but that did not change any behaviour. Besides, why would
it? It doesn't seem logical to me why you would get an exception exactly
there, if the resultset isn't closed and you don't get an exception on
the subsequent next() and rs.getXXX() calls after the second execute.
> Thus sharing connections across threads is just problematic unless the
> application performs synchronization and/or has very good knowledge of
> what others threads are doing at all times. Any application will just be
> less error prone if it uses separate connections for separate threads,
> isn't one of the reasons to use a relational database to not have to
> worry about data synchronization issues? This of course is not specific
> to Derby, the JDBC spec specifies this behavviour.
I agree. Just to be clear, I am not arguing that anyone should code
applications that way, I'm just trying to figure out exactly how Derby
behaves.
Although sharing connections between unrelated threads which perform
different tasks is not a good idea, I can imagine there are cases where
one would benefit from having multiple open resultsets on a connection
with autocommit off. This should be allowed, right? Also, Derby should
be agnostic to whether these result sets are processed in different threads.
Actually, this is what I do in jdbcapi/SetQueryTimeoutTest.java, in
order to verify that the correct statement is affected by the timeout
when multiple statements are executing concurrently. What's strange is
that this works in embedded mode but fails in client/server mode (I'm
about to submit an implementation of setQueryTimeout for the client
driver). The little test program I wrote yesterday works in both modes,
however. I don't see what I'm doing differently - in both programs I
have autocommit off, HOLD_CURSORS_OVER_COMMIT (by default), and have two
threads that prepare and execute a select query on the same connection,
then traverse the result sets. Still one program gets a closed resultset
in client/server mode, the other doesn't. I must be overlooking
something. Any ideas?
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: I need some advice to choose database for an upcomming job
Posted by Craig L Russell <Cr...@Sun.COM>.
Michael,
On Nov 8, 2005, at 11:18 AM, Michael Segel wrote:
>
> Geez,
> I guess I should have quoted from the spec, rather than the first JDBC
> tutorial, in which the author's did quote exactly that.
>
> This is not to say that junior is wrong.
Where do you get off calling people "junior"?
I find it offensive.
Please try to be professional and cut out the ad hominems.
Craig
> I do believe him that the 3.0 spec
> probably does say that.
>
> This is a problem that occurs when people don't think about their
> design and
> merely regurgitate what they've read. Or that they rush a design
> without
> thinking about it first.
>
> As pointed out, JDBC 4.0 spec does seem to correct this.
>
> But hey, what do I know. I'm not paid to play here. ;-)
>
> -G
>
>
> --
> Michael Segel
> Principal
> MSCC
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: I need some advice to choose database for an upcomming job
Posted by "Lance J. Andersen" <La...@Sun.COM>.
>>This has never been the intent in JDBC since its inception, From the
>>1.0.2 spec:
>>
>>Multi-threading
>>We require that all operations on all the java.sql objects be
>>multi-thread safe and able to cope
>>correctly with having several threads simultaneously calling the same
>>object.
>>Some drivers may allow more concurrent execution than others. Developers
>>can assume fully
>>concurrent execution; if the driver requires some form of
>>synchronization, it will provide it.
>>The only difference visible to the developer will be that applications
>>will run with reduced concurrency.
>>For example, two Statements on the same Connection can be executed
>>concurrently and their
>>ResultSets can be processed concurrently (from the perspective of the
>>developer). Some drivers
>>will provide this full concurrency. Others may execute one statement and
>>wait until it completes
>>before sending the next.
>>
>>
>>HTH
>>
>>-lance
>>
>>
>>
>
>Geez,
>I guess I should have quoted from the spec, rather than the first JDBC
>tutorial, in which the author's did quote exactly that.
>
>This is not to say that junior is wrong. I do believe him that the 3.0 spec
>probably does say that.
>
>This is a problem that occurs when people don't think about their design and
>merely regurgitate what they've read. Or that they rush a design without
>thinking about it first.
>
>As pointed out, JDBC 4.0 spec does seem to correct this.
>
>But hey, what do I know. I'm not paid to play here. ;-)
>
>-G
>
>
>
Yes the tutorial does not replace the spec and i am working to clarify
things such as this in the JDBC 4 spec
lance
Re: I need some advice to choose database for an upcomming job
Posted by Michael Segel <ms...@segel.com>.
On Tuesday 08 November 2005 08:50, Lance J. Andersen wrote:
> Daniel John Debrunner wrote:
> >Dag H. Wanvik wrote:
> >>Hi,
> >>
> >>>>>>>"Daniel" == Daniel John Debrunner <dj...@debrunners.com> wrote:
> >>
> >>Daniel>
> >>
> >>Daniel> I think there was a long discussion on this about six months ago,
> >> lead Daniel> by Phil Wilder. He was trying to clarify autocommit mode
> >> and held cursor Daniel> behaviour in the JDBC spec. It's a little
> >> unclear, as for example, Daniel> section 10.1 was not updated for held
> >> cursors. That was a case where the Daniel> wiki would have been useful,
> >> to present a summary of the current discussion. Daniel>
> >>Daniel> And as you say, I think that discussion was driven by differences
> >> in the Daniel> client and embedded drivers in this area.
> >>
> >>I talked to Lance about the spec being vague on this issue, and the
> >>new draft for JDBC 4.0
> >>(http://www.jcp.org/aboutJava/communityprocess/edr/jsr221/index2.html)
> >>
> >>has a new and clearer wording:
> >>>16.2.5 Closing a ResultSet Object
> >>>
> >>>A ResultSet object is explicitly closed when
> >>> - The close method on the ResultSet is executed, thereby releasing any
> >>> external resources
> >>> - The Statement or Connection object that produced the ResultSet is
> >>> explictly closed
> >>>
> >>>A ResultSet object is implicitly closed when
> >>> - The associated Statement object is re-executed
> >>> - The ResultSet is created with a Holdability of
> >>> CLOSE_CURSORS_AT_COMMIT and an implicit or explicit commit occurs
> >>>
> >>>Note: Some JDBC driver implementations may also implicitly close the
> >>>ResultSet when the ResultSet type is TYPE_FORWARD_ONLY and the next
> >>>method of ResultSet returns false.
> >>
> >>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, From the
> 1.0.2 spec:
>
> Multi-threading
> We require that all operations on all the java.sql objects be
> multi-thread safe and able to cope
> correctly with having several threads simultaneously calling the same
> object.
> Some drivers may allow more concurrent execution than others. Developers
> can assume fully
> concurrent execution; if the driver requires some form of
> synchronization, it will provide it.
> The only difference visible to the developer will be that applications
> will run with reduced concurrency.
> For example, two Statements on the same Connection can be executed
> concurrently and their
> ResultSets can be processed concurrently (from the perspective of the
> developer). Some drivers
> will provide this full concurrency. Others may execute one statement and
> wait until it completes
> before sending the next.
>
>
> HTH
>
> -lance
>
Geez,
I guess I should have quoted from the spec, rather than the first JDBC
tutorial, in which the author's did quote exactly that.
This is not to say that junior is wrong. I do believe him that the 3.0 spec
probably does say that.
This is a problem that occurs when people don't think about their design and
merely regurgitate what they've read. Or that they rush a design without
thinking about it first.
As pointed out, JDBC 4.0 spec does seem to correct this.
But hey, what do I know. I'm not paid to play here. ;-)
-G
--
Michael Segel
Principal
MSCC
Re: I need some advice to choose database for an upcomming job
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Michael Segel wrote:
>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.
>>
>>
>>
>Right.
>No argument there. And I think that you've missed the point I was trying to
>make.
>
>Its not how the database vendor handles transactions, but rather how JDBC
>handles them.
>
>
No that is not totally true as the database implementation is key in the
functionality that a JDBC driver can provide.
Some databases start an implicit transaction for a given SQL statement
and there is no way for the driver to know about this completely.
This issue was discussed at great lengths in the JDBC EG already and due
to the various semantic differences between vendors, this is another
scenario where your milage varies.
>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.
>(Read: ONE TRANSACTION CONTEXT FOR THE ENTIRE OBJECT)
>
>
>
not always, see above
>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... ;-)
>
>
>So
>
>
>>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
>spec.
>
>You have really two ways to look at this.
>
>1) The JDBC spec is fine, and the Connection object should manage the
>transactions.
>
>
JDBC, like ODBC is an API to assist with writing portable code, but
they are only a plumbing layer. Limitations
are set on top of these APIs based on the functionality provided by the
backend.
These APIs do a good job in helping with portability, but it is not
perfect, especially corner cases such as this.
>
>
Re: I need some advice to choose database for an upcomming job
Posted by Michael Segel <ms...@segel.com>.
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.
>
Right.
No argument there. And I think that you've missed the point I was trying to
make.
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.
(Read: ONE TRANSACTION CONTEXT FOR THE ENTIRE 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... ;-)
So
> 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
spec.
You have really two ways to look at this.
1) The JDBC spec is fine, and the Connection object should manage the
transactions.
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
transactions....
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. ;-)
-G
--
Michael Segel
Principal
MSCC
Re: I need some advice to choose database for an upcomming job
Posted by Will Senn <wi...@comcast.net>.
Michael said:
>> Will said:
>> However, that being said, my 2 cents is that it's totally nuts to commit
>> anything after an error, unless you don't care that your datastore is in
>> an unknown state (you being the database vendor). The argument that the
>> app developer might want it that way... sheesh harkens back to the days
>> of writing linear incongruent pseudo random number generators using
>> BASIC's integer overflow characteristics... Cool, but infinitely
>> frightening (non-portable, monster-unmaintainable) at the same time.
>>
>> -Will
>>
> I don't think that anyone is suggesting that.
>
Upon a reread, I see the error of my ways. The premise was that the
client disconnected while the transaction was active. To commit or not
to commit, that is the question... I can see it both ways, if the
transaction was completely transmitted to the database, what relevance
the client connection? It might be nice if you could resume a broken
connection, then it'd be up to the application (or network layer) to
maintain connectivity and resume broken connections and restore session
state. In the absence of session resumption... If you don't do atomic
commits... etc... It'd be a real bummer to open a transaction, get
disconnected and not know what exactly occurred in the db...
Later,
Will
> In further thought of my suggestion, I guess the database vendors would have
> to do some work. Considering what I am thinking would force them to rethink
> what is meant by a connection relative to a transaction, and how to allow for
> multiple non-nested concurrent transactions to occur within the same
> connection context.
>
> But again, I don't think that its going to be rocket science.
>
>
Re: I need some advice to choose database for an upcomming job
Posted by Michael Segel <ms...@segel.com>.
On Wednesday 09 November 2005 09:14, Will Senn wrote:
> Total and complete insanity on the vendors part, but a truthful
> assessment nonetheless. Gone are the days of bullet proof specs, or are
> they? Could it be that they were never here? Sadly not, although it may
> have seemed so, back in the day. The exponential increase in complexity
> has exposed a great number of flaws in what might have seemed solid back
> when.
>
You really don't want to get in to the discussion of bullet proof specs. ;-)
> However, that being said, my 2 cents is that it's totally nuts to commit
> anything after an error, unless you don't care that your datastore is in
> an unknown state (you being the database vendor). The argument that the
> app developer might want it that way... sheesh harkens back to the days
> of writing linear incongruent pseudo random number generators using
> BASIC's integer overflow characteristics... Cool, but infinitely
> frightening (non-portable, monster-unmaintainable) at the same time.
>
> -Will
I don't think that anyone is suggesting that.
In further thought of my suggestion, I guess the database vendors would have
to do some work. Considering what I am thinking would force them to rethink
what is meant by a connection relative to a transaction, and how to allow for
multiple non-nested concurrent transactions to occur within the same
connection context.
But again, I don't think that its going to be rocket science.
--
Michael Segel
Principal
MSCC
Re: I need some advice to choose database for an upcomming job
Posted by Will Senn <wi...@comcast.net>.
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.
>
> 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)
Total and complete insanity on the vendors part, but a truthful
assessment nonetheless. Gone are the days of bullet proof specs, or are
they? Could it be that they were never here? Sadly not, although it may
have seemed so, back in the day. The exponential increase in complexity
has exposed a great number of flaws in what might have seemed solid back
when.
However, that being said, my 2 cents is that it's totally nuts to commit
anything after an error, unless you don't care that your datastore is in
an unknown state (you being the database vendor). The argument that the
app developer might want it that way... sheesh harkens back to the days
of writing linear incongruent pseudo random number generators using
BASIC's integer overflow characteristics... Cool, but infinitely
frightening (non-portable, monster-unmaintainable) at the same time.
-Will
Re: I need some advice to choose database for an upcomming job
Posted by "Lance J. Andersen" <La...@Sun.COM>.
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.
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)
>
>
>>>JDBC 3
>>>
>>>"For Select statements, the statement is complete when the associated
>>>result set is
>>>closed. The result set is closed as soon as one of the following occurs:
>>>[...]
>>>- another Statement object is executed on the same connection"
>>>
>>>Pretty explicit in both cases, only a single statement can be active in
>>>auto-commit mode. JDBC 4.0 is proposing a change that multiple
>>>statements, possibly including updates, can be active when in
>>>auto-commit mode. I wonder if that was really the intent of EG for these
>>>changes?
>>>
>>>
>>The JDBC 3 spec intermixed when a ResultSet was closed with auto-commit
>>semantics and these both are seperate scenarios. These should have been
>>seperated.
>>
>>
>>The verbage in JDBC 3 WRT to having another Statement executed on the
>>same connection was not explict WRT auto commit as it was written and
>>led to confusion.
>>
>>
>>
>
>You can interpret this as follows:
>
>Those who wrote the JDBC spec did not conceive that someone would want to have
>multiple separate sets of transactions occurring within the same connection.
>(If you're going to run transactions then you should design your system to
>create a separate connection per thread.)
>
>Simply put GIGO....
>
>Or
>
>Those who wrote the spec left the design open, allowing individuals to
>implement the spec as they saw fit. Allowing programmers enough room to hang
>themselves. If you consider the C programming language, its fairly open and
>doesn't protect the novice programmer from him/her self. Its a double edged
>sword.
>
>
>
>>Perhaps 1 more bullet could be added to clarify this *specific* scenario
>>as to the state of the ResultSet, i will see.
>>
>>
>>
>Try and consider your audience.
>Sometimes more is less. Or is it that less is more?
>
>But hey, what do I know?
>I just saw that CA was bowing out of the Opensource DB world.... ;-)
>
>
This to me is a corner case that a good programmer
would not get themselves into.
I may not get agreement from the EG on this scenario, outside of your
milage may vary.
>-G
>
>
>
Re: I need some advice to choose database for an upcomming job
Posted by Michael Segel <ms...@segel.com>.
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. ;-)
> >JDBC 3
> >
> >"For Select statements, the statement is complete when the associated
> >result set is
> >closed. The result set is closed as soon as one of the following occurs:
> >[...]
> >- another Statement object is executed on the same connection"
> >
> >Pretty explicit in both cases, only a single statement can be active in
> >auto-commit mode. JDBC 4.0 is proposing a change that multiple
> >statements, possibly including updates, can be active when in
> >auto-commit mode. I wonder if that was really the intent of EG for these
> >changes?
>
> The JDBC 3 spec intermixed when a ResultSet was closed with auto-commit
> semantics and these both are seperate scenarios. These should have been
> seperated.
>
>
> The verbage in JDBC 3 WRT to having another Statement executed on the
> same connection was not explict WRT auto commit as it was written and
> led to confusion.
>
You can interpret this as follows:
Those who wrote the JDBC spec did not conceive that someone would want to have
multiple separate sets of transactions occurring within the same connection.
(If you're going to run transactions then you should design your system to
create a separate connection per thread.)
Simply put GIGO....
Or
Those who wrote the spec left the design open, allowing individuals to
implement the spec as they saw fit. Allowing programmers enough room to hang
themselves. If you consider the C programming language, its fairly open and
doesn't protect the novice programmer from him/her self. Its a double edged
sword.
> Perhaps 1 more bullet could be added to clarify this *specific* scenario
> as to the state of the ResultSet, i will see.
>
Try and consider your audience.
Sometimes more is less. Or is it that less is more?
But hey, what do I know?
I just saw that CA was bowing out of the Opensource DB world.... ;-)
-G
--
Michael Segel
Principal
MSCC
Re: I need some advice to choose database for an upcomming job
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Lance J. Andersen wrote:
> The JDBC 3 spec intermixed when a ResultSet was closed with auto-commit
> semantics and these both are seperate scenarios. These should have been
> seperated.
Yep.
> The verbage in JDBC 3 WRT to having another Statement executed on the
> same connection was not explict WRT auto commit as it was written and
> led to confusion.
I see that now, I'd always read that as only if auto-commit was true,
but I can see that it's not clear.
>
> Perhaps 1 more bullet could be added to clarify this *specific* scenario
> as to the state of the ResultSet, i will see.
Sounds good.
Thanks,
Dan.
Re: I need some advice to choose database for an upcomming job
Posted by "Lance J. Andersen" <La...@Sun.COM>.
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)
>JDBC 3
>
>"For Select statements, the statement is complete when the associated
>result set is
>closed. The result set is closed as soon as one of the following occurs:
>[...]
>- another Statement object is executed on the same connection"
>
>Pretty explicit in both cases, only a single statement can be active in
>auto-commit mode. JDBC 4.0 is proposing a change that multiple
>statements, possibly including updates, can be active when in
>auto-commit mode. I wonder if that was really the intent of EG for these
>changes?
>
>
The JDBC 3 spec intermixed when a ResultSet was closed with auto-commit
semantics and these both are seperate scenarios. These should have been
seperated.
The verbage in JDBC 3 WRT to having another Statement executed on the
same connection was not explict WRT auto commit as it was written and
led to confusion.
Perhaps 1 more bullet could be added to clarify this *specific* scenario
as to the state of the ResultSet, i will see.
>
>
>>>From the 1.0.2 spec:
>>
>>
>
>
>
>>Multi-threading
>>We require that all operations on all the java.sql objects be
>>multi-thread safe and able to cope
>>correctly with having several threads simultaneously calling the same
>>object.
>>Some drivers may allow more concurrent execution than others. Developers
>>can assume fully
>>concurrent execution; if the driver requires some form of
>>synchronization, it will provide it.
>>The only difference visible to the developer will be that applications
>>will run with reduced concurrency.
>>For example, two Statements on the same Connection can be executed
>>concurrently and their
>>ResultSets can be processed concurrently (from the perspective of the
>>developer). Some drivers
>>will provide this full concurrency. Others may execute one statement and
>>wait until it completes
>>before sending the next.
>>
>>
>
>Derby/Cloudscape has always been completely compliant with the above
>multi-threading section. This section is also not in conflict with the
>current defined JDBC 3 auto commit behaviour.
>
>Dan.
>
>
>
Re: I need some advice to choose database for an upcomming job
Posted by Daniel John Debrunner <dj...@debrunners.com>.
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)."
JDBC 3
"For Select statements, the statement is complete when the associated
result set is
closed. The result set is closed as soon as one of the following occurs:
[...]
- another Statement object is executed on the same connection"
Pretty explicit in both cases, only a single statement can be active in
auto-commit mode. JDBC 4.0 is proposing a change that multiple
statements, possibly including updates, can be active when in
auto-commit mode. I wonder if that was really the intent of EG for these
changes?
> From the 1.0.2 spec:
> Multi-threading
> We require that all operations on all the java.sql objects be
> multi-thread safe and able to cope
> correctly with having several threads simultaneously calling the same
> object.
> Some drivers may allow more concurrent execution than others. Developers
> can assume fully
> concurrent execution; if the driver requires some form of
> synchronization, it will provide it.
> The only difference visible to the developer will be that applications
> will run with reduced concurrency.
> For example, two Statements on the same Connection can be executed
> concurrently and their
> ResultSets can be processed concurrently (from the perspective of the
> developer). Some drivers
> will provide this full concurrency. Others may execute one statement and
> wait until it completes
> before sending the next.
Derby/Cloudscape has always been completely compliant with the above
multi-threading section. This section is also not in conflict with the
current defined JDBC 3 auto commit behaviour.
Dan.
Re: I need some advice to choose database for an upcomming job
Posted by "Lance J. Andersen" <La...@Sun.COM>.
Daniel John Debrunner wrote:
>Dag H. Wanvik wrote:
>
>
>
>>Hi,
>>
>>
>>
>>
>>>>>>>"Daniel" == Daniel John Debrunner <dj...@debrunners.com> wrote:
>>>>>>>
>>>>>>>
>>Daniel>
>>
>>Daniel> I think there was a long discussion on this about six months ago, lead
>>Daniel> by Phil Wilder. He was trying to clarify autocommit mode and held cursor
>>Daniel> behaviour in the JDBC spec. It's a little unclear, as for example,
>>Daniel> section 10.1 was not updated for held cursors. That was a case where the
>>Daniel> wiki would have been useful, to present a summary of the current discussion.
>>Daniel>
>>Daniel> And as you say, I think that discussion was driven by differences in the
>>Daniel> client and embedded drivers in this area.
>>
>>I talked to Lance about the spec being vague on this issue, and the
>>new draft for JDBC 4.0
>>(http://www.jcp.org/aboutJava/communityprocess/edr/jsr221/index2.html)
>>has a new and clearer wording:
>>
>>
>>
>>
>>>16.2.5 Closing a ResultSet Object
>>>
>>>A ResultSet object is explicitly closed when
>>> - The close method on the ResultSet is executed, thereby releasing any
>>> external resources
>>> - The Statement or Connection object that produced the ResultSet is
>>> explictly closed
>>>
>>>A ResultSet object is implicitly closed when
>>> - The associated Statement object is re-executed
>>> - The ResultSet is created with a Holdability of
>>> CLOSE_CURSORS_AT_COMMIT and an implicit or explicit commit occurs
>>>
>>>Note: Some JDBC driver implementations may also implicitly close the
>>>ResultSet when the ResultSet type is TYPE_FORWARD_ONLY and the next
>>>method of ResultSet returns false.
>>>
>>>
>>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, From the
1.0.2 spec:
Multi-threading
We require that all operations on all the java.sql objects be
multi-thread safe and able to cope
correctly with having several threads simultaneously calling the same
object.
Some drivers may allow more concurrent execution than others. Developers
can assume fully
concurrent execution; if the driver requires some form of
synchronization, it will provide it.
The only difference visible to the developer will be that applications
will run with reduced concurrency.
For example, two Statements on the same Connection can be executed
concurrently and their
ResultSets can be processed concurrently (from the perspective of the
developer). Some drivers
will provide this full concurrency. Others may execute one statement and
wait until it completes
before sending the next.
HTH
-lance
>> I find the new wording more logical,
>>and I think we should try to move the implementation of Derby towards
>>this behavior.
>>
>>¹Auto-commit is triggered for select statements when the result set is
>>closed, making that statement complete (Section 9.1)
>>
>>
>
>Interesting on the 4.0 stuff, that's a change in behaviour from 3.0, I
>wonder if any applications would rely on the 3.0 executing on another
>statement behaviour. Of course we shouldn't change until 4.0 is
>official, and then do we support two behaviours, one for 3.0 and one for
>4.0? Probably a future discussion on the dev list.
>
>The 4.0 changes will allow multiple open statements per Connection in
>auto commit mode, as opposed to a single one in 3.0. That's an
>interesting change, I wonder if this will break any assumption in Derby
>in autocommit mode. I'm also not sure that the changes help in the area
>driving this discussion, allowing multiple threads to share a single
>connection. It seems that the same basic problem exist, without
>application synchronization, activity on one thread will modify the
>state of the objects being used by the other thread.
>
>Dan.
>
>
>
Re: I need some advice to choose database for an upcomming job
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Dag H. Wanvik wrote:
> Hi,
>
>
>>>>>>"Daniel" == Daniel John Debrunner <dj...@debrunners.com> wrote:
>
> Daniel>
>
> Daniel> I think there was a long discussion on this about six months ago, lead
> Daniel> by Phil Wilder. He was trying to clarify autocommit mode and held cursor
> Daniel> behaviour in the JDBC spec. It's a little unclear, as for example,
> Daniel> section 10.1 was not updated for held cursors. That was a case where the
> Daniel> wiki would have been useful, to present a summary of the current discussion.
> Daniel>
> Daniel> And as you say, I think that discussion was driven by differences in the
> Daniel> client and embedded drivers in this area.
>
> I talked to Lance about the spec being vague on this issue, and the
> new draft for JDBC 4.0
> (http://www.jcp.org/aboutJava/communityprocess/edr/jsr221/index2.html)
> has a new and clearer wording:
>
>
>>16.2.5 Closing a ResultSet Object
>>
>>A ResultSet object is explicitly closed when
>> - The close method on the ResultSet is executed, thereby releasing any
>> external resources
>> - The Statement or Connection object that produced the ResultSet is
>> explictly closed
>>
>>A ResultSet object is implicitly closed when
>> - The associated Statement object is re-executed
>> - The ResultSet is created with a Holdability of
>> CLOSE_CURSORS_AT_COMMIT and an implicit or explicit commit occurs
>>
>>Note: Some JDBC driver implementations may also implicitly close the
>>ResultSet when the ResultSet type is TYPE_FORWARD_ONLY and the next
>>method of ResultSet returns false.
>
>
> Note that executing a statement on *another* statement object in the
> same connection no longer closes a result set, and hence should no
> longer trigger an auto-commit¹. I find the new wording more logical,
> and I think we should try to move the implementation of Derby towards
> this behavior.
>
> ¹Auto-commit is triggered for select statements when the result set is
> closed, making that statement complete (Section 9.1)
Interesting on the 4.0 stuff, that's a change in behaviour from 3.0, I
wonder if any applications would rely on the 3.0 executing on another
statement behaviour. Of course we shouldn't change until 4.0 is
official, and then do we support two behaviours, one for 3.0 and one for
4.0? Probably a future discussion on the dev list.
The 4.0 changes will allow multiple open statements per Connection in
auto commit mode, as opposed to a single one in 3.0. That's an
interesting change, I wonder if this will break any assumption in Derby
in autocommit mode. I'm also not sure that the changes help in the area
driving this discussion, allowing multiple threads to share a single
connection. It seems that the same basic problem exist, without
application synchronization, activity on one thread will modify the
state of the objects being used by the other thread.
Dan.
Re: I need some advice to choose database for an upcomming job
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Hi,
>>>>> "Daniel" == Daniel John Debrunner <dj...@debrunners.com> wrote:
Daniel>
Daniel> I think there was a long discussion on this about six months ago, lead
Daniel> by Phil Wilder. He was trying to clarify autocommit mode and held cursor
Daniel> behaviour in the JDBC spec. It's a little unclear, as for example,
Daniel> section 10.1 was not updated for held cursors. That was a case where the
Daniel> wiki would have been useful, to present a summary of the current discussion.
Daniel>
Daniel> And as you say, I think that discussion was driven by differences in the
Daniel> client and embedded drivers in this area.
I talked to Lance about the spec being vague on this issue, and the
new draft for JDBC 4.0
(http://www.jcp.org/aboutJava/communityprocess/edr/jsr221/index2.html)
has a new and clearer wording:
> 16.2.5 Closing a ResultSet Object
>
> A ResultSet object is explicitly closed when
> - The close method on the ResultSet is executed, thereby releasing any
> external resources
> - The Statement or Connection object that produced the ResultSet is
> explictly closed
>
> A ResultSet object is implicitly closed when
> - The associated Statement object is re-executed
> - The ResultSet is created with a Holdability of
> CLOSE_CURSORS_AT_COMMIT and an implicit or explicit commit occurs
>
> Note: Some JDBC driver implementations may also implicitly close the
> ResultSet when the ResultSet type is TYPE_FORWARD_ONLY and the next
> method of ResultSet returns false.
Note that executing a statement on *another* statement object in the
same connection no longer closes a result set, and hence should no
longer trigger an auto-commit¹. I find the new wording more logical,
and I think we should try to move the implementation of Derby towards
this behavior.
¹Auto-commit is triggered for select statements when the result set is
closed, making that statement complete (Section 9.1)
Dag
Re: I need some advice to choose database for an upcomming job
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Oyvind.Bakksjo@Sun.COM wrote:
> Daniel John Debrunner wrote:
>
>> Oyvind.Bakksjo@Sun.COM wrote:
>>
>>> Note B: If you're running with autocommit OFF, you should definately not
>>> use the same connection object in multiple simultaneous requests (either
>>> use synchronization or create multiple connections).
>>
>>
>>
>> This is true even with automcommit on. Multiple threads using the same
>> connection with ResultSets will mess with each other, as per the JDBC
>> sprc. An executeQuery by one thread will close any ResultSet any other
>> thread is processing.
>
>
> Could you elaborate on this?
>
> I created a small test program which Prepares two statements in separate
> threads, but uses the same connection object. I execute statement A and
> read a few rows from the resultset in thread 1, execute statement B and
> read a few rows from it in thread 2, do a little interleaved reading,
> then read the rest of the rows from both resultsets in their respective
> threads. Got no exception, indicating that no result set was closed.
>
> I ran this in autocommit mode in both embedded and client/server mode.
>
> Code excerpt below, in case my explanation of the test program was unclear:
>
> t1.prepareStatement();
> t2.prepareStatement();
> t1.executeQuery();
> t1.showRow();
> t1.showRow();
> t1.showRow();
> t2.executeQuery();
> t2.showRow();
> t2.showRow();
> t2.showRow();
> t1.showRow();
> t2.showRow();
> t1.showAll();
> t2.showAll();
>
> I have seen some differing behaviour with respect to this in
> client/server and embedded mode before (that's why I made the test), but
> I couldn't reproduce any issue now.
-----------------------
JDBC 3.0 - Section 10.1.
A commit occurs when a statement is complete in auto-commit mode.
For Select statements, the statement is complete when the associated
result set is closed. The result set is closed as soon as one of the
following occurs:
...
another Statement object is executed on the same connection
------------------------
So in your example the call to t2.executeQuery() should cause a commit
on the connection which will close the ResultSet for t1.
Your code probably has ResultSets which are held
HOLD_CURSORS_OVER_COMMIT (which is the default) and thus are not closed
by the commit. In addition I guess you showRow() does a next() and then
the rs.gerXXX()? I think if you called t2.executeQuery() between a
next() and the rs.getXXX() calls on the other thread, I think you will
see problems.
Also most likely if you change the ResultSet type to
CLOSE_CURSORS_AT_COMMIT on commit you will see problems.
Thus sharing connections across threads is just problematic unless the
application performs synchronization and/or has very good knowledge of
what others threads are doing at all times. Any application will just be
less error prone if it uses separate connections for separate threads,
isn't one of the reasons to use a relational database to not have to
worry about data synchronization issues? This of course is not specific
to Derby, the JDBC spec specifies this behavviour.
I think there was a long discussion on this about six months ago, lead
by Phil Wilder. He was trying to clarify autocommit mode and held cursor
behaviour in the JDBC spec. It's a little unclear, as for example,
section 10.1 was not updated for held cursors. That was a case where the
wiki would have been useful, to present a summary of the current discussion.
And as you say, I think that discussion was driven by differences in the
client and embedded drivers in this area.
Dan.
Re: I need some advice to choose database for an upcomming job
Posted by Mike Matrigali <mi...@sbcglobal.net>.
with test interactions like this it is important to specify whether
the result sets were marked close on commit or not. There are a number
of different ways in jdbc to do this.
Oyvind.Bakksjo@Sun.COM wrote:
> Daniel John Debrunner wrote:
>
>> Oyvind.Bakksjo@Sun.COM wrote:
>>
>>> Note B: If you're running with autocommit OFF, you should definately not
>>> use the same connection object in multiple simultaneous requests (either
>>> use synchronization or create multiple connections).
>>
>>
>>
>> This is true even with automcommit on. Multiple threads using the same
>> connection with ResultSets will mess with each other, as per the JDBC
>> sprc. An executeQuery by one thread will close any ResultSet any other
>> thread is processing.
>
>
> Could you elaborate on this?
>
> I created a small test program which Prepares two statements in separate
> threads, but uses the same connection object. I execute statement A and
> read a few rows from the resultset in thread 1, execute statement B and
> read a few rows from it in thread 2, do a little interleaved reading,
> then read the rest of the rows from both resultsets in their respective
> threads. Got no exception, indicating that no result set was closed.
>
> I ran this in autocommit mode in both embedded and client/server mode.
>
> Code excerpt below, in case my explanation of the test program was unclear:
>
> t1.prepareStatement();
> t2.prepareStatement();
> t1.executeQuery();
> t1.showRow();
> t1.showRow();
> t1.showRow();
> t2.executeQuery();
> t2.showRow();
> t2.showRow();
> t2.showRow();
> t1.showRow();
> t2.showRow();
> t1.showAll();
> t2.showAll();
>
> I have seen some differing behaviour with respect to this in
> client/server and embedded mode before (that's why I made the test), but
> I couldn't reproduce any issue now.
>
Re: I need some advice to choose database for an upcomming job
Posted by Oy...@Sun.COM.
Daniel John Debrunner wrote:
> Oyvind.Bakksjo@Sun.COM wrote:
>
>>Note B: If you're running with autocommit OFF, you should definately not
>>use the same connection object in multiple simultaneous requests (either
>>use synchronization or create multiple connections).
>
>
> This is true even with automcommit on. Multiple threads using the same
> connection with ResultSets will mess with each other, as per the JDBC
> sprc. An executeQuery by one thread will close any ResultSet any other
> thread is processing.
Could you elaborate on this?
I created a small test program which Prepares two statements in separate
threads, but uses the same connection object. I execute statement A and
read a few rows from the resultset in thread 1, execute statement B and
read a few rows from it in thread 2, do a little interleaved reading,
then read the rest of the rows from both resultsets in their respective
threads. Got no exception, indicating that no result set was closed.
I ran this in autocommit mode in both embedded and client/server mode.
Code excerpt below, in case my explanation of the test program was unclear:
t1.prepareStatement();
t2.prepareStatement();
t1.executeQuery();
t1.showRow();
t1.showRow();
t1.showRow();
t2.executeQuery();
t2.showRow();
t2.showRow();
t2.showRow();
t1.showRow();
t2.showRow();
t1.showAll();
t2.showAll();
I have seen some differing behaviour with respect to this in
client/server and embedded mode before (that's why I made the test), but
I couldn't reproduce any issue now.
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: I need some advice to choose database for an upcomming job
Posted by Daniel John Debrunner <dj...@debrunners.com>.
Oyvind.Bakksjo@Sun.COM wrote:
> Note A: You may want to create and use more than one connection to
> increase performance if you have many simultaneous requests.
I would not recommend a single connection for any simultaneous requests.
> Note B: If you're running with autocommit OFF, you should definately not
> use the same connection object in multiple simultaneous requests (either
> use synchronization or create multiple connections).
This is true even with automcommit on. Multiple threads using the same
connection with ResultSets will mess with each other, as per the JDBC
sprc. An executeQuery by one thread will close any ResultSet any other
thread is processing.
If you are running Derby embedded then the cost of creating a connection
per request will be much lower than traditional client server databases,
and most likely will be a small fraction of the cost of the query or update.
If you are concerned about the cost of multiple connections, then you
can use connection pooling.
Dan.
Re: I need some advice to choose database for an upcomming job
Posted by Oy...@Sun.COM.
Legolas Woodland wrote:
> Hi
> Thank you for the Blog entry ,
> but i could not understand how i should do the integration based on your
> blog comment.
> I tried Integration senario from developerWorks ,
> I need it for a real world high transaction system (23-30 Tra / second)
> so i think developersWork scenario is not good for me.
>
> can you explain more pleas ? (to Oyvind Bakksjo)
You should be able to fully embed Derby into your web application
without any need to configure your servlet container *and* get decent
performance by doing the following:
* Place derby.jar in your web application's lib directory.
* Use derby in embedded mode inside your application (use
"jdbc:derby:<dbname>[;attributes...]" as the connection url)
* For performance, you need to avoid creating a new connection with each
request. Therefore, you should a) boot the database when your web
application is loaded (and shut it down when the web app is unloaded)
and b) reuse existing connection(s).
* To do a)
- create a class in your web app which implements the
javax.servlet.ServletContextListener interface (see
http://java.sun.com/j2ee/1.4/docs/api/javax/servlet/ServletContextListener.html).
The class should have a public no-arg constructor. Say, the class is
called MyListener.
- Inside the <web-app> section of your deployment descriptor, add
"<listener><listener-class>MyListener</listener-class></listener>".
- The class must implement two methods. In contextInitialized(), you
boot the database by getting a connection to it (possibly with
";create=true", if necessary). Stuff that connection aside for later use
by calling 'sce.getServletContext().setAttribute("derbyconnection",
connection);'. In contextDestroyed, fetch the existing connection (with
'sce.getServletContext().getAttribute("derbyconnection");') and close
that connection. Shut down the database by connecting again with the
"shutdown=true" attribute in the url.
* To do b)
- When a request is received (say, HttpServlet.doGet() is invoked),
you get the existing connection by calling
'getServletContext().getAttribute("derbyconnection");'. Use this
connection for your database work.
Note A: You may want to create and use more than one connection to
increase performance if you have many simultaneous requests.
Note B: If you're running with autocommit OFF, you should definately not
use the same connection object in multiple simultaneous requests (either
use synchronization or create multiple connections).
I hope this helps.
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: I need some advice to choose database for an upcomming job
Posted by Legolas Woodland <le...@gmail.com>.
Hi
Thank you for the Blog entry ,
but i could not understand how i should do the integration based on your
blog comment.
I tried Integration senario from developerWorks ,
I need it for a real world high transaction system (23-30 Tra / second) so i
think developersWork scenario is not good for me.
can you explain more pleas ? (to Oyvind Bakksjo)
Thank you
On 10/31/05, Oyvind.Bakksjo@sun.com <Oy...@sun.com> wrote:
>
> Jean T. Anderson wrote:
> > Legolas Woodland wrote:
> >
> >> Thank you for references
> >> I read the article of embeding Derby into Tomcat
> >> but in my case i have no full access to server to stop tomcat or
> >> install derby into the server.
> >> I thought Embeding means no installation requirement.
> >> is there any way that i include some derby jar files with my web
> >> application (into lib folder) and use it as embeded DB server ?
> >>
> >
> > Check out Lance Bader's developerWorks article "Integrate Cloudscape
> > Version 10 or Derby with Tomcat":
> >
> >
> http://www.ibm.com/developerworks/db2/library/techarticle/dm-0508bader/index.html
> >
> >
> > His "prototype integration" option doesn't require touching the Tomcat
> > configuration. While his instructions have you stop/start the tomcat
> > server, I don't know if this is strictly necessary to add derby to your
> > existing web application. Does anyone on this list know for sure?
>
> The "prototype integration" incurs a lot of overhead, starting and
> shutting down Derby with each request. The other two integration
> scenarios use Tomcat-specific extensions and require restarting the
> server.
>
> I would like to suggest reading my (short but excellent ;o) blog entry,
> where I describe how one can do lifecycle management in a standard
> (container-agnostic) way, which neither requires access to the Tomcat
> configuration nor stopping/starting the server.
>
>
> http://weblogs.java.net/blog/bakksjo/archive/2005/09/embedding_the_a_1.html
>
> --
> Oyvind Bakksjo
> Sun Microsystems, Database Technology Group
> Trondheim, Norway
> http://weblogs.java.net/blog/bakksjo/
>
Re: I need some advice to choose database for an upcomming job
Posted by Oy...@Sun.COM.
Jean T. Anderson wrote:
> Legolas Woodland wrote:
>
>> Thank you for references
>> I read the article of embeding Derby into Tomcat
>> but in my case i have no full access to server to stop tomcat or
>> install derby into the server.
>> I thought Embeding means no installation requirement.
>> is there any way that i include some derby jar files with my web
>> application (into lib folder) and use it as embeded DB server ?
>>
>
> Check out Lance Bader's developerWorks article "Integrate Cloudscape
> Version 10 or Derby with Tomcat":
>
> http://www.ibm.com/developerworks/db2/library/techarticle/dm-0508bader/index.html
>
>
> His "prototype integration" option doesn't require touching the Tomcat
> configuration. While his instructions have you stop/start the tomcat
> server, I don't know if this is strictly necessary to add derby to your
> existing web application. Does anyone on this list know for sure?
The "prototype integration" incurs a lot of overhead, starting and
shutting down Derby with each request. The other two integration
scenarios use Tomcat-specific extensions and require restarting the server.
I would like to suggest reading my (short but excellent ;o) blog entry,
where I describe how one can do lifecycle management in a standard
(container-agnostic) way, which neither requires access to the Tomcat
configuration nor stopping/starting the server.
http://weblogs.java.net/blog/bakksjo/archive/2005/09/embedding_the_a_1.html
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: I need some advice to choose database for an upcomming job
Posted by Craig L Russell <Cr...@Sun.COM>.
Often, databases are installed in a web server so they are able to be
shared among multiple web applications. Depending on the web server,
installation might require the server to be bounced to take effect,
although many servers can dynamically add resources. The referenced
article calls this "Casual Integration" and "Enterprise Integration".
If the Derby database is intended only for use by a specific web
application (not shared) I don't believe that the web server is even
aware of its use, and therefore no installation, configuration, or
special treatment of the database is needed. The article refers to
this as "Prototype Integration". To the web server, the application
is simply accessing files, so the only thing that's needed is to
configure the application for deployment such that file access is
permitted.
As far as I know, the only thing to watch out for is class loader
conflicts. To avoid possible conflicts among multiple applications
using Derby, the "non-delegate" property of the application should be
used. This has the effect of loading the entire Derby code base into
the application. For efficiency, if multiple applications are going
to use Derby, it might be a good idea to use "delegate" and share the
Derby code, understanding that sharing the code has more requirements
(you need to make sure that the different applications can use the
same version of Derby).
Craig
On Oct 30, 2005, at 10:06 AM, Jean T. Anderson wrote:
> Legolas Woodland wrote:
>
>> Thank you for references
>> I read the article of embeding Derby into Tomcat
>> but in my case i have no full access to server to stop tomcat or
>> install derby into the server.
>> I thought Embeding means no installation requirement.
>> is there any way that i include some derby jar files with my web
>> application (into lib folder) and use it as embeded DB server ?
>>
>
> Check out Lance Bader's developerWorks article "Integrate
> Cloudscape Version 10 or Derby with Tomcat":
>
> http://www.ibm.com/developerworks/db2/library/techarticle/
> dm-0508bader/index.html
>
> His "prototype integration" option doesn't require touching the
> Tomcat configuration. While his instructions have you stop/start
> the tomcat server, I don't know if this is strictly necessary to
> add derby to your existing web application. Does anyone on this
> list know for sure?
>
> -jean
>
>
>
>> Thank you.
>> On 10/29/05, *Jean T. Anderson* <jta@bristowhill.com
>> <ma...@bristowhill.com>> wrote:
>> Legolas Woodland wrote:
>> > Hi
>> > Thank you for reading my post
>> > I should design and implement an application which is going to
>> run on
>> > tomcat 5.5 and Database can be one of derby ,sqlServer ,
>> Mysql.
>> > Problem is that this application should be able to have
>> about 15
>> > -20Transaction in 1 second .They have no extra power servers ,
>> just an
>> > ordinary hosting plan.
>> > my question is : which of this database is better to choose ?
>> > i thought that derby could be better because it can be embeded
>> into my
>> > application .
>> > can derby carry out 15-20 transacion in 1 secod ?is it
>> feasible with
>> > using DAO pattern and plain JDBC ,or it need some other
>> requirement ?
>> > I have Struts and mysql experience I learned derby but i
>> did not
>> use it
>> > yet.
>> > before they ask me about implementing this project they used
>> PHP+Mysql
>> > and it seems that that application Hangs on +10 hits per
>> second
>> > each hit had some Insert one update and 2 select at least.
>> >
>> Dan Debrunner's "Introducing Apache Derby" presentation from
>> ApacheCon
>> US last November might have some helpful info for you. You can
>> download
>> it from http://db.apache.org/derby/binaries/djd_derby_intro.pdf .
>> A "Guidelines" section starts on slide 19. Slide 24 lists 100-500
>> updates per second -- but, of course, your actual performance
>> will
>> depend on the complexity of your transactions.
>> Also you might be interested in an article Stan Bradbury
>> contributed
>> yesterday that shows a way to embed Derby in Tomcat 5.5. You
>> can find it
>> here:
>> http://db.apache.org/derby/integrate/
>> DerbyTomcat5512JPetStor.html
>> <http://db.apache.org/derby/integrate/
>> DerbyTomcat5512JPetStor.html>
>> I hope this helps.
>> regards,
>> -jean
>>
>
>
Re: I need some advice to choose database for an upcomming job
Posted by Kristian Waagan <Kr...@Sun.COM>.
Michael Segel wrote:
> On Wednesday 02 November 2005 09:46, Kristian Waagan wrote:
>
> Ok,
> Silly question on either Sun's or Tomcat.
>
> Is this a potential security risk? What's the default parameters?
> I guess all you need to do is to limit access to that directory....
>
You can disable both dynamic reloading (changes in already deployed
applications) and autodeploy, but I believe the default is that these
features are enabled for both appservers mentioned.
You are also correct in that the security mechanism for these features
is the OS/filesystem access control. It is also possible to specify
autodeploy directories per application, so that developers for that
application don't need access to the main autodeploy directory (for
SJSAS, don't know for Tomcat - anybody?).
> I mean its great for prototyping, but in production?
I browsed some docs, and it is stated that the dynamic reloading feature
may cause some performance degradation.
I suppose many system admins would disable these features in a
production environment.
--
Kristian
>
> Now the reason I'm asking is that I'm being buggered to have a lightweight web
> interface in to an app that I'm working on. (Derby is the backend DB).
> This will allow for a central copy of the DB that will synch with the
> individual copies.
>
> One nice thing about Derby.
> You can have your complete App and the DB all on a simple memory stick.
>
>
>>Hello,
>>
>>Just want to inform that the Sun Java System Application Server actually
>> does redeploy if you overwrite, or touches, the already existing
>>deployment archive in the autodeploy directory.
>>
>>I have not worked much with Tomcat, so I grabbed it to see what it does.
>>I observed that it also redeploys when you overwrite an existing
>>deployment archive in the webapps directory. I tried this on Solaris 10
>>with Tomcat version 5.5.12.
>>
>>This means that you might be able to use the Prototype integration
>>approach to redeploy your application (and have it use Derby) without
>>restarting/refreshing the server. It is at least worth a try :)
>>(you might get stopped by the current server configuration though)
>>
>>
>>
>>
>>Regards,
>>--
>>Kristian
>
>
Re: I need some advice to choose database for an upcomming job
Posted by Michael Segel <ms...@segel.com>.
On Wednesday 02 November 2005 09:46, Kristian Waagan wrote:
Ok,
Silly question on either Sun's or Tomcat.
Is this a potential security risk? What's the default parameters?
I guess all you need to do is to limit access to that directory....
I mean its great for prototyping, but in production?
Now the reason I'm asking is that I'm being buggered to have a lightweight web
interface in to an app that I'm working on. (Derby is the backend DB).
This will allow for a central copy of the DB that will synch with the
individual copies.
One nice thing about Derby.
You can have your complete App and the DB all on a simple memory stick.
> Hello,
>
> Just want to inform that the Sun Java System Application Server actually
> does redeploy if you overwrite, or touches, the already existing
> deployment archive in the autodeploy directory.
>
> I have not worked much with Tomcat, so I grabbed it to see what it does.
> I observed that it also redeploys when you overwrite an existing
> deployment archive in the webapps directory. I tried this on Solaris 10
> with Tomcat version 5.5.12.
>
> This means that you might be able to use the Prototype integration
> approach to redeploy your application (and have it use Derby) without
> restarting/refreshing the server. It is at least worth a try :)
> (you might get stopped by the current server configuration though)
>
>
>
>
> Regards,
> --
> Kristian
--
Michael Segel
Principal
MSCC
Re: I need some advice to choose database for an upcomming job
Posted by Kristian Waagan <Kr...@Sun.COM>.
Michael Segel wrote:
> On Monday 31 October 2005 14:40, Susan Cline wrote:
>
>>As Jean mentioned, if you use the Prototype integration option of the
>>article you can have the derby jar files in the war file. I think you can
>>just replace the war file with the new web application that contains the
>>derby jar file(s) in the lib directory without stopping the Tomcat server.
>>
>>I think Tomcat "sees" new war files (possibly depending on how it is
>>configured) when they are placed in the webapps directory.
>>
>>Susan
>>
>
> Replace the file?
> I would be very suspect if that were the case.
> Usually your application will open a file descriptor to the files at start up.
> (Your new files don't have the same inode (whatever the Microsoft analogy
> is ...) So its not the same file.
>
> This would mean that you would have to "refresh" Tomcat to get it to "see" the
> new file.
>
> Now if you can refresh Tomcat, then you should have the power to start and
> stop Tomcat.
>
> If you mean add it to the directory and then have Tomcat "discover" the new
> files/apps that would be different.
>
>
Hello,
Just want to inform that the Sun Java System Application Server actually
does redeploy if you overwrite, or touches, the already existing
deployment archive in the autodeploy directory.
I have not worked much with Tomcat, so I grabbed it to see what it does.
I observed that it also redeploys when you overwrite an existing
deployment archive in the webapps directory. I tried this on Solaris 10
with Tomcat version 5.5.12.
This means that you might be able to use the Prototype integration
approach to redeploy your application (and have it use Derby) without
restarting/refreshing the server. It is at least worth a try :)
(you might get stopped by the current server configuration though)
Regards,
--
Kristian
Re: I need some advice to choose database for an upcomming job
Posted by Oy...@Sun.COM.
Michael Segel wrote:
> On Monday 31 October 2005 14:40, Susan Cline wrote:
>
>>As Jean mentioned, if you use the Prototype integration option of the
>>article you can have the derby jar files in the war file. I think you can
>>just replace the war file with the new web application that contains the
>>derby jar file(s) in the lib directory without stopping the Tomcat server.
>>
>>I think Tomcat "sees" new war files (possibly depending on how it is
>>configured) when they are placed in the webapps directory.
>>
>>Susan
>>
>
> Replace the file?
> I would be very suspect if that were the case.
> Usually your application will open a file descriptor to the files at start up.
> (Your new files don't have the same inode (whatever the Microsoft analogy
> is ...) So its not the same file.
>
> This would mean that you would have to "refresh" Tomcat to get it to "see" the
> new file.
>
> Now if you can refresh Tomcat, then you should have the power to start and
> stop Tomcat.
>
> If you mean add it to the directory and then have Tomcat "discover" the new
> files/apps that would be different.
This is what the Tomcat website says about hot deployment of web
applications:
Deploying on a running Tomcat server
It is possible to deploy web applications to a running Tomcat server.
If the Host autoDeploy attribute is "true", the Host will attempt
to deploy and update web applications dynamically, as needed, for
example if a new .WAR is dropped into the appBase. For this to work, the
Host needs to have background processing enabled which is the default
configuration.
autoDeploy set to "true" and a running Tomcat allows for:
* Deployment of .WAR files copied into the Host appBase.
* Deployment of exploded web applications which are copied into
the Host appBase.
* Re-deployment of a web application which has already been
deployed from a .WAR when the new .WAR is provided. In this case the
exploded web application is removed, and the .WAR is expanded again.
Note that the explosion will not occur if the Host is configured so that
.WARs are not exploded with a unpackWARs attribute set to "false", in
which case the web application will be simply redeployed as a compressed
archive.
* Re-deployment of a web application if the /WEB-INF/web.xml
file (or any other resource defined as a WatchedResource) is updated.
* Re-deployment of a web application if the Context Descriptor
file from which the web application has been deployed is updated.
* Re-deployment of a web application if a Context Descriptor
file (with a filename corresponding to the Context path of the
previously deployed web application) is added to the
$CATALINA_HOME/conf/[enginename]/[hostname]/ directory.
* Undeployment of a web application if its document base
(docBase) is deleted. Note that on Windows, this assumes that
anti-locking features (see Context configuration) are enabled, otherwise
it is not possible to delete the resources of a running web application.
Note that web application reloading can also be configured in the
loader, in which case loaded classes will be tracked for changes.
My interpretation is that it is actually possible to just replace the
.war file.
--
Oyvind Bakksjo
Sun Microsystems, Database Technology Group
Trondheim, Norway
http://weblogs.java.net/blog/bakksjo/
Re: I need some advice to choose database for an upcomming job
Posted by Michael Segel <ms...@segel.com>.
On Monday 31 October 2005 14:40, Susan Cline wrote:
> As Jean mentioned, if you use the Prototype integration option of the
> article you can have the derby jar files in the war file. I think you can
> just replace the war file with the new web application that contains the
> derby jar file(s) in the lib directory without stopping the Tomcat server.
>
> I think Tomcat "sees" new war files (possibly depending on how it is
> configured) when they are placed in the webapps directory.
>
> Susan
>
Replace the file?
I would be very suspect if that were the case.
Usually your application will open a file descriptor to the files at start up.
(Your new files don't have the same inode (whatever the Microsoft analogy
is ...) So its not the same file.
This would mean that you would have to "refresh" Tomcat to get it to "see" the
new file.
Now if you can refresh Tomcat, then you should have the power to start and
stop Tomcat.
If you mean add it to the directory and then have Tomcat "discover" the new
files/apps that would be different.
--
Michael Segel
Principal
MSCC
Re: I need some advice to choose database for an upcomming job
Posted by Susan Cline <ho...@pacbell.net>.
As Jean mentioned, if you use the Prototype integration option of the article you can have the derby jar files in the war file. I think you can just replace the war file with the new web application that contains the derby jar file(s) in the lib directory without stopping the Tomcat server.
I think Tomcat "sees" new war files (possibly depending on how it is configured) when they are placed in the webapps directory.
Susan
"Jean T. Anderson" <jt...@bristowhill.com> wrote:
Legolas Woodland wrote:
> Thank you for references
> I read the article of embeding Derby into Tomcat
> but in my case i have no full access to server to stop tomcat or
> install derby into the server.
> I thought Embeding means no installation requirement.
> is there any way that i include some derby jar files with my web
> application (into lib folder) and use it as embeded DB server ?
>
Check out Lance Bader's developerWorks article "Integrate Cloudscape
Version 10 or Derby with Tomcat":
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0508bader/index.html
His "prototype integration" option doesn't require touching the Tomcat
configuration. While his instructions have you stop/start the tomcat
server, I don't know if this is strictly necessary to add derby to your
existing web application. Does anyone on this list know for sure?
-jean
> Thank you.
>
>
>
> On 10/29/05, *Jean T. Anderson* > > wrote:
>
> Legolas Woodland wrote:
> > Hi
> > Thank you for reading my post
> > I should design and implement an application which is going to
> run on
> > tomcat 5.5 and Database can be one of derby ,sqlServer , Mysql.
> > Problem is that this application should be able to have about 15
> > -20Transaction in 1 second .They have no extra power servers ,
> just an
> > ordinary hosting plan.
> > my question is : which of this database is better to choose ?
> > i thought that derby could be better because it can be embeded
> into my
> > application .
> > can derby carry out 15-20 transacion in 1 secod ?is it feasible with
> > using DAO pattern and plain JDBC ,or it need some other requirement ?
> > I have Struts and mysql experience I learned derby but i did not
> use it
> > yet.
> > before they ask me about implementing this project they used
> PHP+Mysql
> > and it seems that that application Hangs on +10 hits per second
> > each hit had some Insert one update and 2 select at least.
> >
>
> Dan Debrunner's "Introducing Apache Derby" presentation from ApacheCon
> US last November might have some helpful info for you. You can download
> it from http://db.apache.org/derby/binaries/djd_derby_intro.pdf .
>
> A "Guidelines" section starts on slide 19. Slide 24 lists 100-500
> updates per second -- but, of course, your actual performance will
> depend on the complexity of your transactions.
>
> Also you might be interested in an article Stan Bradbury contributed
> yesterday that shows a way to embed Derby in Tomcat 5.5. You can find it
> here:
>
> http://db.apache.org/derby/integrate/DerbyTomcat5512JPetStor.html
>
>
> I hope this helps.
>
> regards,
>
> -jean
>
>
>
>
Re: I need some advice to choose database for an upcomming job
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Legolas Woodland wrote:
> Thank you for references
> I read the article of embeding Derby into Tomcat
> but in my case i have no full access to server to stop tomcat or
> install derby into the server.
> I thought Embeding means no installation requirement.
> is there any way that i include some derby jar files with my web
> application (into lib folder) and use it as embeded DB server ?
>
Check out Lance Bader's developerWorks article "Integrate Cloudscape
Version 10 or Derby with Tomcat":
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0508bader/index.html
His "prototype integration" option doesn't require touching the Tomcat
configuration. While his instructions have you stop/start the tomcat
server, I don't know if this is strictly necessary to add derby to your
existing web application. Does anyone on this list know for sure?
-jean
> Thank you.
>
>
>
> On 10/29/05, *Jean T. Anderson* <jta@bristowhill.com
> <ma...@bristowhill.com>> wrote:
>
> Legolas Woodland wrote:
> > Hi
> > Thank you for reading my post
> > I should design and implement an application which is going to
> run on
> > tomcat 5.5 and Database can be one of derby ,sqlServer , Mysql.
> > Problem is that this application should be able to have about 15
> > -20Transaction in 1 second .They have no extra power servers ,
> just an
> > ordinary hosting plan.
> > my question is : which of this database is better to choose ?
> > i thought that derby could be better because it can be embeded
> into my
> > application .
> > can derby carry out 15-20 transacion in 1 secod ?is it feasible with
> > using DAO pattern and plain JDBC ,or it need some other requirement ?
> > I have Struts and mysql experience I learned derby but i did not
> use it
> > yet.
> > before they ask me about implementing this project they used
> PHP+Mysql
> > and it seems that that application Hangs on +10 hits per second
> > each hit had some Insert one update and 2 select at least.
> >
>
> Dan Debrunner's "Introducing Apache Derby" presentation from ApacheCon
> US last November might have some helpful info for you. You can download
> it from http://db.apache.org/derby/binaries/djd_derby_intro.pdf .
>
> A "Guidelines" section starts on slide 19. Slide 24 lists 100-500
> updates per second -- but, of course, your actual performance will
> depend on the complexity of your transactions.
>
> Also you might be interested in an article Stan Bradbury contributed
> yesterday that shows a way to embed Derby in Tomcat 5.5. You can find it
> here:
>
> http://db.apache.org/derby/integrate/DerbyTomcat5512JPetStor.html
> <http://db.apache.org/derby/integrate/DerbyTomcat5512JPetStor.html>
>
> I hope this helps.
>
> regards,
>
> -jean
>
>
>
>
Re: I need some advice to choose database for an upcomming job
Posted by Legolas Woodland <le...@gmail.com>.
Thank you for references
I read the article of embeding Derby into Tomcat
but in my case i have no full access to server to stop tomcat or install
derby into the server.
I thought Embeding means no installation requirement.
is there any way that i include some derby jar files with my web application
(into lib folder) and use it as embeded DB server ?
Thank you.
On 10/29/05, Jean T. Anderson <jt...@bristowhill.com> wrote:
>
> Legolas Woodland wrote:
> > Hi
> > Thank you for reading my post
> > I should design and implement an application which is going to run on
> > tomcat 5.5 and Database can be one of derby ,sqlServer , Mysql.
> > Problem is that this application should be able to have about 15
> > -20Transaction in 1 second .They have no extra power servers , just an
> > ordinary hosting plan.
> > my question is : which of this database is better to choose ?
> > i thought that derby could be better because it can be embeded into my
> > application .
> > can derby carry out 15-20 transacion in 1 secod ?is it feasible with
> > using DAO pattern and plain JDBC ,or it need some other requirement ?
> > I have Struts and mysql experience I learned derby but i did not use it
> > yet.
> > before they ask me about implementing this project they used PHP+Mysql
> > and it seems that that application Hangs on +10 hits per second
> > each hit had some Insert one update and 2 select at least.
> >
>
> Dan Debrunner's "Introducing Apache Derby" presentation from ApacheCon
> US last November might have some helpful info for you. You can download
> it from http://db.apache.org/derby/binaries/djd_derby_intro.pdf .
>
> A "Guidelines" section starts on slide 19. Slide 24 lists 100-500
> updates per second -- but, of course, your actual performance will
> depend on the complexity of your transactions.
>
> Also you might be interested in an article Stan Bradbury contributed
> yesterday that shows a way to embed Derby in Tomcat 5.5. You can find it
> here:
>
> http://db.apache.org/derby/integrate/DerbyTomcat5512JPetStor.html
>
> I hope this helps.
>
> regards,
>
> -jean
>
>
>
>
Re: I need some advice to choose database for an upcomming job
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Legolas Woodland wrote:
> Hi
> Thank you for reading my post
> I should design and implement an application which is going to run on
> tomcat 5.5 and Database can be one of derby ,sqlServer , Mysql.
> Problem is that this application should be able to have about 15
> -20Transaction in 1 second .They have no extra power servers , just an
> ordinary hosting plan.
> my question is : which of this database is better to choose ?
> i thought that derby could be better because it can be embeded into my
> application .
> can derby carry out 15-20 transacion in 1 secod ?is it feasible with
> using DAO pattern and plain JDBC ,or it need some other requirement ?
> I have Struts and mysql experience I learned derby but i did not use it
> yet.
> before they ask me about implementing this project they used PHP+Mysql
> and it seems that that application Hangs on +10 hits per second
> each hit had some Insert one update and 2 select at least.
>
Dan Debrunner's "Introducing Apache Derby" presentation from ApacheCon
US last November might have some helpful info for you. You can download
it from http://db.apache.org/derby/binaries/djd_derby_intro.pdf .
A "Guidelines" section starts on slide 19. Slide 24 lists 100-500
updates per second -- but, of course, your actual performance will
depend on the complexity of your transactions.
Also you might be interested in an article Stan Bradbury contributed
yesterday that shows a way to embed Derby in Tomcat 5.5. You can find it
here:
http://db.apache.org/derby/integrate/DerbyTomcat5512JPetStor.html
I hope this helps.
regards,
-jean