Обсуждение: Driver confused by explicit transactions

It appears there is a somewhat on-going bug in the ODBC driver
(07.02.0005 in this case) concerning explicit transactions.  The
following sequence demonstrates it.

Begin transaction   (OK)
Execute update statement(s)   (OK, records hidden from other
transactions)
Commit transaction    (OK, records appear to other transactions)
Execute update statement(s)    (OK, but records hidden from other
transactions, should be visible)
Commit transaction    (OK, records appear to other transactions)

I'm going to dive into the code and see what I see, but wanted to report
the symptoms so others can look too.  I suspect the driver is failing to
turn auto-commit mode back on after the explicit commit, and is starting
a new transaction for statements following the commit when it should not
be, which do not get committed automatically.

Glen Parker
glenebob@nwlink.com

It appears that this is fixed simply by having PGAPI_Transact() call
CC_set_autocommit_on(conn) after sending and verifying success of a
commit/abort statement to the server.  That makes the end of
PGAPI_Transact() look like this in my copy:


        if (!ok)
        {
            CC_on_abort(conn, NO_TRANS);
            CC_log_error(func, "", conn);
            return SQL_ERROR;
        }

        /* get (back) into auto-commit mode */
        CC_set_autocommit_on(conn);
    }
    return SQL_SUCCESS;
}


Glen Parker
glenebob@nwlink.com




> It appears there is a somewhat on-going bug in the ODBC driver
> (07.02.0005 in this case) concerning explicit transactions.  The
> following sequence demonstrates it.
>
> Begin transaction   (OK)
> Execute update statement(s)   (OK, records hidden from other
> transactions)
> Commit transaction    (OK, records appear to other transactions)
> Execute update statement(s)    (OK, but records hidden from other
> transactions, should be visible)
> Commit transaction    (OK, records appear to other transactions)
>
> I'm going to dive into the code and see what I see, but
> wanted to report
> the symptoms so others can look too.  I suspect the driver is
> failing to
> turn auto-commit mode back on after the explicit commit, and
> is starting
> a new transaction for statements following the commit when it
> should not
> be, which do not get committed automatically.
>
> Glen Parker
> glenebob@nwlink.com
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
http://archives.postgresql.org

> -----Original Message-----
> From: Glen Parker
>
> It appears there is a somewhat on-going bug in the ODBC driver
> (07.02.0005 in this case) concerning explicit transactions.  The
> following sequence demonstrates it.

What do you mean by explicit transactions ? i. e

> Begin transaction   (OK)

   How do you begin the transaction ?

> Execute update statement(s)   (OK, records hidden from other
> transactions)
> Commit transaction    (OK, records appear to other transactions)

  And how do you commit the transaction ?

regards,
Hiroshi Inoue

> > It appears there is a somewhat on-going bug in the ODBC driver
> > (07.02.0005 in this case) concerning explicit transactions.  The
> > following sequence demonstrates it.
>
> What do you mean by explicit transactions ? i. e
>
> > Begin transaction   (OK)
>
>    How do you begin the transaction ?

By turning off auto-commit with a call to SQLSetConnectOption().

> > Execute update statement(s)   (OK, records hidden from other
> > transactions)
> > Commit transaction    (OK, records appear to other transactions)
>
>   And how do you commit the transaction ?

With a call to SQLTransact() to either commit or abort transaction.

Glen

> -----Original Message-----
> From: Glen Parker [mailto:glenebob@nwlink.com]
>
> > > It appears there is a somewhat on-going bug in the ODBC driver
> > > (07.02.0005 in this case) concerning explicit transactions.  The
> > > following sequence demonstrates it.
> >
> > What do you mean by explicit transactions ? i. e
> >
> > > Begin transaction   (OK)
> >
> >    How do you begin the transaction ?
>
> By turning off auto-commit with a call to SQLSetConnectOption().
>
> > > Execute update statement(s)   (OK, records hidden from other
> > > transactions)
> > > Commit transaction    (OK, records appear to other transactions)
> >
> >   And how do you commit the transaction ?
>
> With a call to SQLTransact() to either commit or abort transaction.

Should SQLTransact() turn on auto-commit ?

regards,
Hiroshi Inoue