Re: Implicit Transactions Problem Revealed

Il giorno dom, 11/05/2008 alle 01.07 -0400, Federico Alves ha scritto:
> Implicit Transactions Problem Debugged
> 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. How
> does it know one case from another? It looks whether the freetds.conf has an
> entry with the name of the server and "tds version = 8.0". If this is true,
> then it must be Microsoft SQL Server, and the implicit transactions are off.
> I know because I proved it, using only Tsql. I connected once without the
> "tds version = 8.0" and the SQL debugger showed "implicit transactions ON".
> I disconnected, put back the option in freetds.conf and reconnected,
> straight from tsql, no odbc, and there it was, "implicit transactions off".
> Therefore, being that I can reproduce it at will, I offered Frediano Ziglio
> to come into my machine and see with his own eyes, but I still think that
> anybody with access to SQL server and the profiler tools can reproduce it.
> In fact, Microsoft showed me the problem after many hours of research in my
> SQL server. I spent $3000 in support fees to come to this knowledge. I chose
> to remove that "tds version = 8.0" in my freetds.conf for one valid reason.
> If it is there, any perl script that uses Stored Procedure parameters
> (in-out), does not work. For example, this perl code stops working if the
> option "tds version = 8.0" is included:
> do {
>       while(my $dat = $sth->fetch) {
> print "TYPE $sth->{syb_result_type}\n";
> print "Data @$dat[0] , @$dat[1] , @$dat[2] \n";
>          if($sth->{syb_result_type} == 4042) { # it's a PARAM result
>            print "Number:  $dat->[0]  \n";
>             print "Varpar:  $dat->[1]  \n";
>          }
>       }
>    } while($sth->{syb_more_results});
> 
> Any parameters syb_result_type=4042 are ignored if "tds version = 8.0", and
> that is wrong because SQL 2005 actually must be using the same
> "syb_type_number", since it works fine. I think that we need to address this
> and have it both ways: parameter passing and "tds version = 8.0" I offer to
> show this to anybody who wants to come into my box. I will start the
> debugger on the right side of the screen, and connect using Tsql on the left
> side. You will see what happens immediately. It is not the server, or the
> app, it is freedts who chooses. But that is fine, because we can fix it.
> 
> 

Well... this perhaps explains the problem. ODBC and OleDB have a setting
(option_flag2) that prevents server from setting implicit_transaction to
on (see http://msdn.microsoft.com/en-us/library/ms176031.aspx).

>From source 

$ cgrep option_flag2
./include/tds.h:	unsigned char option_flag2;
./src/dblib/dblib.c:	dbproc->tds_socket->option_flag2 &= ~0x02;	/* we're not an ODBC driver */
./src/tds/login.c:	unsigned char option_flag2 = tds->option_flag2;
./src/tds/login.c:		option_flag2 |= 0x80;	/* enable domain login security                     */
./src/tds/login.c:	tds_put_byte(tds, option_flag2);
./src/tds/mem.c:	tds_socket->option_flag2 = 0x03;

deafult is 3 (2 is ODBC, 1 is "change to initial language must succeed",
see
http://freetds.cvs.sourceforge.net/*checkout*/freetds/freetds/doc/tds.html?revision=1.38#login7)
Now tsql and DBD::Sybase use dblib library that is affected by "user
options" setting about implicit_transaction (see link above). I bet you
have this setting turned on (see
http://lists.ibiblio.org/pipermail/freetds/2008q2/023253.html on how to
check it).
dblib is correctly affected by this flag so is not an API error that
it's the expected behavior if you set "I want implicit_transaction on".
To fix the problem you can:
- turn the flag to off
- use a patched version (that however is not standard)
- set implicit_transaction explicitly to off after login
- use ODBC (in Perl case use DBD::ODBC)

If 'user options' does not contain implicit_transaction on, well... this
is a problem!

Regards,
  Frediano