Thread: commit problem
my Java developers are telling me, the recent JDBC drivers are throwing an exception if they inadvertantly issue a commit() on an autocommit connection. this is causing a bunch of problems for us, we have a huge code base of java jdbc software which was originally written for Oracle, and it assumes that read only operations are NOT starting a transaction block (apparently Oracle only starts a transaction block on DML like insert/update/delete/select for update/etc.). We have long running threads which just do SELECTs and others that do transactions, some which can do either of these depending on external events. We've tried to make the SELECT only connections 'autocommit' so they don't start transaction blocks, and we've tried to enforce .Commit() on other situations, but the code is complex enough that sometimes the wires get crossed, and a Commit() s done on an autocommit connection. All of this is to prevent gigenormous data bloating from week-long <IDLE> in transactions. in the JDBC releases for PG 8.x, this was quietly ignored. On the new 9.x JDBC releases, this crashes with an ugly exception. My very-oracle-centric lead SQL developer is trying to use this as yet another excuse not to use Postgres as in his opinion, Oracle just 'does what you want'. ok, it does what HE wants, meh. Meanwhile, these random crashes that only show up under full production volume workloads (at our deployment sites in Asia) are freaking out the operations people, who ALSO are becoming afraid of Postgres. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
So ... looking at the API this is clearly unambiguous. A rarity in JDBC
from the api specs ...
void commit()
throws SQLException
Makes all changes made since the previous commit/rollback permanent
and releases any database locks currently held by this Connection
object. This method should be used only when auto-commit mode has been
disabled.
Throws:
SQLException - if a database access error occurs, this method is
called while participating in a distributed transaction, if this
method is called on a closed conection or this Connection object is in
auto-commit mode
See Also:
setAutoCommit(boolean)
So it would appear oracle is breaking their own API.
Thoughts ?
<tongue in cheek>
BTW, you might suggest to your Oracle centric dev that he pay the
difference for the licenses if he really wants Oracle
</tongue in cheek>
Dave Cramer
dave.cramer(at)credativ(dot)ca
http://www.credativ.ca
On Thu, Oct 25, 2012 at 4:12 PM, John R Pierce <pierce@hogranch.com> wrote:
> my Java developers are telling me, the recent JDBC drivers are throwing an
> exception if they inadvertantly issue a commit() on an autocommit
> connection. this is causing a bunch of problems for us, we have a huge
> code base of java jdbc software which was originally written for Oracle, and
> it assumes that read only operations are NOT starting a transaction block
> (apparently Oracle only starts a transaction block on DML like
> insert/update/delete/select for update/etc.). We have long running threads
> which just do SELECTs and others that do transactions, some which can do
> either of these depending on external events. We've tried to make the
> SELECT only connections 'autocommit' so they don't start transaction blocks,
> and we've tried to enforce .Commit() on other situations, but the code is
> complex enough that sometimes the wires get crossed, and a Commit() s done
> on an autocommit connection. All of this is to prevent gigenormous
> data bloating from week-long <IDLE> in transactions.
>
> in the JDBC releases for PG 8.x, this was quietly ignored. On the new 9.x
> JDBC releases, this crashes with an ugly exception.
>
> My very-oracle-centric lead SQL developer is trying to use this as yet
> another excuse not to use Postgres as in his opinion, Oracle just 'does what
> you want'. ok, it does what HE wants, meh.
>
> Meanwhile, these random crashes that only show up under full production
> volume workloads (at our deployment sites in Asia) are freaking out the
> operations people, who ALSO are becoming afraid of Postgres.
>
>
>
> --
> john r pierce N 37, W 122
> santa cruz ca mid-left coast
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
John R Pierce wrote: > my Java developers are telling me, the recent JDBC drivers are > throwing an exception if they inadvertantly issue a commit() on an > autocommit connection. Which is what seems to be required by the API: http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#commit%28%29 > My very-oracle-centric lead SQL developer is trying to use this as > yet another excuse not to use Postgres as in his opinion, Oracle > just 'does what you want'. Or rather, whatever it wants, regardless of standards and API definitions. An API currently published by... Oracle? > Meanwhile, these random crashes that only show up under full > production volume workloads (at our deployment sites in Asia) are > freaking out the operations people, who ALSO are becoming afraid of > Postgres. Sounds ugly. What I wonder is why the your development team doesn't route all transaction completions through a common method. I have always seen that as the only sane way to do it. Then you have one place where you can change: conn.commit(); to: if (conn.getAutoCommit() == false) conn.commit(); I would be afraid of developers who seem to be either unable to manage development in a way that prevents such problems, or willing to let preventable problems happen to try to "prove a point". I hope you have managers above this lead SQL developer who will ask him why he hasn't made this work when so many other developers can do so. Is he simply not as good as the developers at all these other companies who solve such problems, or is he unwilling to do so? Is there a 3rd option there? -Kevin
On 10/25/12 1:45 PM, Kevin Grittner wrote: > Sounds ugly. What I wonder is why the your development team doesn't > route all transaction completions through a common method. I have > always seen that as the only sane way to do it. Then you have one > place where you can change: > > conn.commit(); because my developers don't think simple reads ARE a transaction. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
John R Pierce wrote on 25.10.2012 22:12: > my Java developers are telling me, the recent JDBC drivers are > throwing an exception if they inadvertantly issue a commit() on an > autocommit connection. Just for the record: Apparently there is no consensus on this behaviour (even though it's clearly required according to the JDBC specs) MySQL, the jTDS driver for SQL Server and Firebird also throw an exeption in that case. The Microsoft driver, DB2, Derby, h2 and HSQLDB do not throw an exception just like the Oracle driver.
John R Pierce wrote on 25.10.2012 23:48: > because my developers don't think simple reads ARE a transaction. > Then they haven't read the concepts manual of Oracle. Because a SELECT statement *is* (and starts) a transaction in Oracle as well.
John R Pierce wrote on 25.10.2012 22:12: > in the JDBC releases for PG 8.x, this was quietly ignored. On the > new 9.x JDBC releases, this crashes with an ugly exception. You can always get the driver's source, change it and compile your own version. Something you can't do with Oracle in case it doesn't do what you want.
John R Pierce wrote: > my developers don't think simple reads ARE a transaction. Now you've piqued my interest. To run multiple simple selects which should see a consistent view of the data, wouldn't they expect they could setTransactionIsolation() to TRANSACTION_REPEATABLE_READ or TRANSACTION_SERIALIZABLE and setAutoCommit() false to see a stable view of the data? How would they say they were done with that view and wanted a fresh one for the next batch of SELECT statements? -Kevin
John R Pierce wrote: > On 10/25/12 1:45 PM, Kevin Grittner wrote: > > Sounds ugly. What I wonder is why the your development team doesn't > > route all transaction completions through a common method. I have > > always seen that as the only sane way to do it. Then you have one > > place where you can change: > > > > conn.commit(); > > because my developers don't think simple reads ARE a transaction. > > > > -- > john r pierce N 37, W 122 > santa cruz ca mid-left coast > > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc