Re: Behavior of Connection.commit()



Wes Clark wrote:

I'm interested in Microsoft's response. Does anyone know what the JDBC spec says here? Does it say "commit" and "rollback" and other SQL commands that affect the transaction state are not allowed? If so, should the driver throw an error? That would also require parsing, of course.

How about in a batch of statements? How sure can the driver ever be about the state of the transaction? 

We will await MS's response. In the meantime, the spec says nothing. It also
doesn't say what happens if the JDBC driver sends "shutdown" SQL to the DBMS
either. A batch of statements doesn't add any more complexity. The driver must
assume the transactional status is as it was set the last time via JDBC. Drivers
will never parse SQL for transactional semantics, let alone presume to react
to them, and stored procedures or even triggers could easily subvert any such
effort.
I can promise you as a former JDBC spec member and co-author of the first
commercial JDBC drivers on the market for MS SQLServer, Sybase, Oracle, and
Informix, and as a DBMS guy (joe@xxxxxxxxxx '88-'96) that you should always
define, control and terminate a tx with only one API and in one logical place.
Good luck, I want you to be successful.
Joe Weinstein at BEA Systems

"Joe Weinstein" wrote:



Wes Clark wrote:


It turns out in very limited circumstances in a few test programs, we were sending a "commit" sql statement to the database. A developer found this in a trace we send in. This was messing up the driver. And I don't think this is related to most of the discussion on this thread.

I would suggest that if the driver needs to keep track of the state of the transaction, it would be advisable to look for SQL statements that would affect that state. I don’t think the JDBC spec would say you can’t commit a transaction using the SQL. 

I'd take the other position. The load imposed by the driver having to parse all the SQL,
looking for commits, rollbacks etc in SQL would be onerous and inappropriate. It would
be clearly poor style to distribute control of transactions between JDBC and SQL.
Procedures could have 'commit' in them and the driver could never know.


I imagine the other drivers do that very thing. Do you accept this as a “bug” and/or should I enter this somewhere else? 

They don't. I highly recommend controling your transactions within
a top-level method that does all the tx work and then calls
commit() or rollback, or letting a tx-capable container do it all for
you.
Joe Weinstein at BEA Systems


Thank you for helping us get back on the road.




.

Relevant Pages

  • Re: Behavior of Connection.commit()
    ... transaction count on the server is what we expect at that point in time.... ... Does anyone know what the JDBC ... >> should the driver throw an error? ... > doesn't say what happens if the JDBC driver sends "shutdown" SQL to the ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Locking and Delay in a Bottleneck
    ... that should avoid the gap issue on rollback, ... SQL Server MVP ... the saving transaction, this journal voucher is having a header table ... number in a table whereupon you commit. ...
    (microsoft.public.sqlserver.programming)
  • Re: Driver AutoCommit issue
    ... Isn't the XA driver for the distributed transaction, ... the connection pool was getting created. ... When in the code do you do a commit? ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Trigger does not seem to fire from front end or enterprise manager
    ... there is no need to even do COMMIT in a trigger. ... > the transaction will be committed as the statement completes. ... > against the inserted and deleted tables inside your trigger code. ... run a profiler trace and you will see what SQL EM submits. ...
    (microsoft.public.sqlserver.programming)
  • Re: Driver AutoCommit issue
    ... Isn't the XA driver for the distributed transaction, I need to install the path sqljdbc_xa.dll and xa_install.sql before using the XA driver. ... I created a TX aware datasource using com.microsoft.sqlserver.jdbc.SQLServerDriver and tried to get the connection from the TX aware data source. ... I set the auto commit to false on connection as soon as I get the connection from the datasoruce. ...
    (microsoft.public.sqlserver.jdbcdriver)
Loading