[freetds] Implicit Transactions Problem Revealed

James K. Lowden jklowden at freetds.org
Sun May 11 11:25:13 EDT 2008

Federico Alves wrote:
> Freetds changes the "autocommit" behavior according to one fact: if it
> thinks is connecting to Sybase, then it turns "implicit transactions"
> ON, because Sybase apparently requires such thing. If it thinks that it
> is connecting to MS SQL Server, then it turns "implicit transactions"
> OFF. 

Simply untrue, Frederico.  

$ TDSVER=4.2 tsql -S $S -U $U -P $P
locale is "C"
locale charset is "646"
1> select @@options
2> go

1> select @@version
2> go

Microsoft SQL Server  2000 - 8.00.384 (Intel X86) 
        May 23 2001 00:02:52 
        Copyright (c) 1988-2000 Microsoft Corporation
        Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


So let's put that theory to rest, shall we?  FreeTDS is not setting
autocommit on or off by itself.  

This is a free software project.  You can look at the code.  You can
examine the whole conversation with the server by looking at the TDSDUMP
log.  It's no use propounding theories without data when the open system
contradicts you!  

Now, could we look at your database?  If you repeat the above, do you see
the same thing?  If not, could you tell me the value of the config_value
column on the user_options row when you run sp_configure?  Mine is zero. 
I think yours is something where ($value & 2) == 2.  

$ (export TDSVER=4.2; printf 'sp_configure\ngo\n' \
  | tsql -S $S -U $U -P $P -D FreeTDS | grep user.options)
Default database being set to FreeTDS
user options    0       32767   0       0
cf. http://msdn.microsoft.com/en-us/library/ms176031.aspx

To emphasize what Frediano said: the login packet has a bit in its options
field that tells the server the connection is coming from and ODBC client.
 We turn that bit off for db-lib connections *only* (which "user option"
IMPLICIT_TRANSACTIONS is supposed to control).  Microsoft of course
doesn't define how ct-lib (which DBD::Sybase uses) clients should behave. 
We choose to make ct-lib and ODBC connections appear the same to the
server by leaving that bit in the login packet OFF.  

To repeat: a ct-lib connection appears to the server as if it's being made
from an ODBC application. (The same is true for a tsql connection, btw.) 

Keep in mind that TDS 4.2 ODBC clients haven't been made for something
like 10 years.  We're in rare territory.  

I don't know why your server is putting the connection into
IMPLICIT_TRANSACTIONS mode when you connect from an "old ODBC" client. 
But that's what's happening.  


More information about the FreeTDS mailing list