Re: Autocommit, Implicit Transactions, and Connection Pools

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance



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 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: I am having a problem in my asp in the recorsets running at iis 6.0 and sql 2000. Sometimes it
    ... > and if I do the same sql querys using query analizer, the sql server ... maintaining your code know what each recordset contains? ... Always use an explicit connection object. ...
    (microsoft.public.inetserver.asp.general)
  • Re: Running SQL Native Client
    ... is there an updated Native Client Driver that needs to be ... installed on machines that connect to that server through ODBC? ... There is an update version of SQL Native Client that comes with service ... but you can still run the RTM version of the driver against SP3. ...
    (microsoft.public.sqlserver.setup)
  • Re: Access -> SQL
    ... Here is my driver. ... SQL Native Client ... This driver was installed by SQL Server Managemnet Studio 2005 ...
    (microsoft.public.access.modulesdaovba)