Re: Autocommit, Implicit Transactions, and Connection Pools



Joe Weinstein wrote:



Wes Clark wrote:

I agree that running queries in autocommit mode with doing a commit could lead to unwanted locks. We do extensive performance testing, and haven't run into any problem, though.

My question is what is the difference between setting autocommit on using the method on the connection object, and running a command on the server to turn on implicit connections.


Hi. Maybe nothing. The driver may implement the setAutoCommit(false)
mode by sending the T-SQL to set implicit trasnsactions on.

Does the connection object keep any internal state that would then be out of sync with the server?


Yes it may well. The driver may keep a flag denoting it's autocommit state,
and sending the SQL yourself may be asking to confuse the driver. You can
test it may seeing what getAutoCommit() returns after you send the "set implcit..."
via a statement. That driver flag may or may not be important though. Test whether
a commit or rollback still works after you've snuck around the driver with the
SQL instead of the setAutoCommit() call.

I checked. You should definitely stick to all JDBC calls to do this
work. If you try to do it via SQL, the driver will still think autoCommit
is true, so commit() and rollback() will do nothing.
Joe


I know I can set transaction isolation level also with a command to SQL Server, as well as a method on the connection object, so the same question applies. I guess I'd like to hear from the Microsoft developers.


In the case of the isolation level, it is a purely DBMS internal behavior,
so no driver behavior would change, except getTransactionIsolation() might
be tricked into returning the default value if setTransactionIsolation()
hadn't been called.

Connection.setReadOnly() is documented as "Puts this connection in read-only mode as a hint to the driver to enable database optimizations." So this is not a setting on the server at all.


I can tell you that the MS driver does absolutely nothing with that call
except to throw an exception if the connection is already closed.


.

Relevant Pages

  • Re: XA and 2000 backwards compatibility
    ... Just a quick confirmation that the 2005 JDBC driver does support XA with Sql ... Server 2000 and 2005. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Oracle linked server : wont run if driver AllowInProc is off
    ... We don't want to run the driver in process for the reason you thought: sql ... It's not that i didn't try the driver provided by oracle but i never got it ... > The Oracle Client is required for MSDAORA as well, ... > space for fear it could crash Sql Server. ...
    (microsoft.public.sqlserver.server)
  • Re: Autocommit, Implicit Transactions, and Connection Pools
    ... My question is what is the difference between setting autocommit on using the method on the connection object, and running a command on the server to turn on implicit connections. ... That driver flag may or may not be important though. ... SQL instead of the setAutoCommitcall. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: SQL 2005 Profiler
    ... printer Microsoft Office Document Image Writer is unknown. ... administrator to install the driver before you log in again. ... Note that if the default trace is enabled, it is directed to the SQL Server log folder. ...
    (microsoft.public.sqlserver.security)
  • Re: TDBC documentation, examples, syntax?
    ... current SQL standards. ... for variable binding at the Tcl level. ... API, I'd probably use Oracle's bound session variables and pass ... As a convenience for driver writers, ...
    (comp.lang.tcl)
Loading