Skip to Content.
Sympa Menu

freetds - Re: [freetds] Transaction weirdness

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: John Anderson <ardour AT semiosix.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Transaction weirdness
  • Date: Mon, 13 Oct 2003 15:09:38 -0000

On Mon, 2003-10-13 at 06:19, James K. Lowden wrote:
> > Not wanting to be argumentative about this
>
> I don't think you're being argumentative, John, for the record, and I hope
> you don't think I am.

I don't think that. I believe it's better to err on the side of
politeness when emailing people I don't know ;-)

> In the log I saw the commit text being issued before the results had been
> fetched, and I saw the succeeding results arrive subsequently, so it was
> easy to explain what happened. Only now are we establishing whether our
> ODBC driver is doing the right thing.

I should've been more clear that the log was produced through an ODBC
driver.

> > http://msdn.microsoft.com/library/default.asp?url=/library/en-
> > us/odbc/htm/odch14pr_8.asp
> ...
> > Committing or rolling back a transaction has the following effect on
> > cursors and access plans:
> >
> > * All cursors are closed, and access plans for prepared
> > statements on that connection are deleted.
> > * All cursors are closed, and access plans for prepared
> > statements on that connection remain intact.
> > * All cursors remain open, and access plans for prepared
> > statements on that connection remain intact.
>
> Clarification: that's an OR clause. It will have one of those effects,
> depending I think on the driver's implementation.

That's how I interpret it too.

> Microsoft has this to say about SQLEndTran in the SQL Server 7.0 Books
> Online:
>
> "By default, the Microsoft® SQL Server(tm) ODBC driver closes a
> statement's
> associated cursor when SQLEndTran commits or rolls back an operation.
> Server
> cursors remain open after this operation if SQL_COPT_SS_PRESERVE_CURSORS
> has
> been set to SQL_PC_ON."
>
> Are we talking about server-side cursors here, and if so, what's the state
> of SQL_COPT_SS_PRESERVE_CURSORS?

Ummm, that's not a constant I'm familiar with. I'm looking at it from an
ODBC point of view.

> > Which to me implies that there's no need to explicitly close a cursor
> > before committing or rolling back a transaction, because the call to
> > SqlEndTran will do it for me - if that's the option the driver
> > implements.
>
> I'll buy that.
>
> > And the driver will also give me back the relevant value
> > from SqlGetInfo with SQL_CURSOR_COMMIT_BEHAVIOR.
>
> Does the FreeTDS driver return SQL_CB_CLOSE?

To confirm what Peter said - odbc.c:3902

case SQL_CURSOR_COMMIT_BEHAVIOR:
/* currently cursors are not supported however sql server close
automaticly cursors on commit */
USIVAL = SQL_CB_CLOSE;
break;
case SQL_CURSOR_ROLLBACK_BEHAVIOR:
USIVAL = SQL_CB_CLOSE;
break;

> +++
>
> I think I know why M$ chose the behavior they did: there's no other
> choice with SQL Server. If results are in the pipeline (as they will
> be with cursors), there's no way to transmit the COMMIT request to a
> SQL Server without first fetching/cancelling the results. Fetching
> could take time and isn't implied by the commit. Cancelling is faster
> and obviously invalidates the cursor(s), so closing them is the
> natural choice.

Makes sense to me.

> It looks to me that you've discovered a bug. We can't blindly pass
> the COMMIT request; we have to close any and all cursors first. To
> demonstrate that clearly, and to facilitate getting it fixed, I'm sure
> it would help if you submitted a unit test with the most minimal
> illustration of the behavior. I myself don't plan any work on the
> ODBC driver, but I'm sure Frediano would appreciate a test.

I'll have to haul the relevant code out of a set of c++ wrapper classes.
I'll try to get something to you in the next few days.

> Oy. And Brian thought writing an ODBC driver would be easy....

Well, thank goodness for that. If he hadn't my life would be
considerably more difficult.

bye
John






Archive powered by MHonArc 2.6.24.

Top of Page