Rollbacks, plus CONNECT/ROLLBACK WORK statements in log

[Former Member]

Running ASA 8.0.2, Delphi 7 clients, I'm trying to determine what is causing unwanted rollbacks within our application. Somehow, a transaction is opened and held open for long periods during which our users process many updates and inserts, all of which are rolled back when our application is exited. The times that these transactions are opened are not times when our application is opening an explicit transaction, but when the user is within "auto-commit" contexts. The only strange thing I see in the log are large blocks of Connect/Rollback statements that look like this:

--CONNECT-1034-023695297648-CMSUser-2010-12-14 13:39
--ROLLBACK-1034-023695297806
ROLLBACK WORK
go
--CONNECT-1056-023695297813-CMSUser-2010-12-14 13:39
--ROLLBACK-1056-023695297971
ROLLBACK WORK
go
--CONNECT-1057-023695297978-CMSUser-2010-12-14 13:39
--ROLLBACK-1057-023695298136
ROLLBACK WORK
go
--CONNECT-1003-023695298143-CMSUser-2010-12-14 13:39
--ROLLBACK-1003-023695298301
ROLLBACK WORK
go
--CONNECT-1007-023695298308-CMSUser-2010-12-14 13:39
--ROLLBACK-1007-023695298466
ROLLBACK WORK
go


In the particular database log where the above snippet was found, these blocks are sometimes happening multiples times per minute.
Does this in some way point to disconnect/connect actions taking place between server and client?  Any idea how this could contribute to a "rogue" transaction state?
I know, we are running a very old version, and are hoping to upgrade to v12 very soon.
Edit/Update for clarification on the problem:
Thank you for your comments so far.  I think I may have muddied the issue by mentioning the Connect/rollback entries in the log, as i'm able to see my real problem happen without any of that present.  Let me try to clarify the real problem...
We do some very basic database access, basic enough that we aren't handling transactions explicitly in the particular cases where the problem arises.  We are using a query component to INSERT a record, but more of a pass-through operation as we are simply using the query object to issue our own INSERT, not something that we're relying on an in-memory table to post for us.  If I browse a database log where this INSERT operation is done consistently, day-in-day-out for weeks or months, the INSERT is followed by a COMMIT, which in my limited knowledge I have assumed was an ODBC autocommit (the dataset component is using the ASA8 odbc libary).  Then, out of no where, the COMMIT is missing, and all the other INSERTs and UPDATES that follow are missing their COMMITs as well, for everything that user does until they exit our app and everything is rolled back.  If I look back through the log to find the last operation that was performed by the user before the initial INSERT, I invariably find a COMMIT.
What i'm trying to say is we have not started an explicit transaction, but a transaction has been started nonetheless and this transaction stays open for the duration of the user's session.
Has anyone ever experienced this?  Any ODBC driver fixes over the course of 5 versions that may have addressed our problem?
I've really reached the end of my rope on this one.  If I have to analyze another log file regarding this issue I might lose it. 🙂
Update:  Wow, I just noticed how popular this post is.  In the end, it came down to programmer error.  I centralized and logged all start/commit transaction calls and finally found a discrepancy that led me down a path of execution that skipped a commit.  SA was just doing what I told it to do, working just fine.

[VolkerBarth]

Any chance to find out whether your app uses autocommit (which is used in ODBC by default) and may change to manual commit mode? Calling ODBC's SQLGetConnectAttr with the SQL_ATTR_AUTOCOMMIT attribute might give a clue. (I don't know whether Delphi has an easier way to query this attribute.)

[former_SQLA_member1694874]

Have you tried a different delphi component to make sure it is the odbc driver and not just some odd behaviour from the component ?

[former_SQLA_member1694880]

I always use an explicit transaction handling, be it Delphi, C# or PowerBuilder, so I've never encountered similar issues. I'm afraid you'll have to find out how your data access components (which one BTW) handle transactions.

[Former Member]

When this started happening about 3 years ago, I wanted to blame the BDE (Borland Database Engine). I have since converted our app to use a completely different data access layer (AnyDAC), and the problem is still happening. The common layer for each is that ODBC is the underlying data access mechanism.

I have stepped through the code within AnyDAC that performs the odbc calls, and can see that without an explicit transaction in place, it simply issues a SQLFreeStmt after executing my insert, resulting in a COMMIT to appear in the db log.

[ian_mchardy]

This is likely a non-issue in SA 10 and newer. An optimization was added to the client to make a COMMIT or ROLLBACK a NO-OP if the last operation was a COMMIT or ROLLBACK.

Note this optimization only applies to using the API's COMMIT/ROLLBACK mechanism (for example, ODBC's SQLEndTran function), not if you prepare and execute a COMMIT or ROLLBACK (using the API's COMMIT/ROLLBACK mechanism is preferable).

---

Actually in your particular case, this optimization is probably not relevant. Something (and I'm pretty sure is not in the SA client code) is causing extra connections. And if a connection disconnects without a commit or rollback the default is a rollback.

[VolkerBarth]

Ian, what does "not if you prepare and execute a COMMIT or ROLLBACK" mean? Does it mean the commit is done as executing the actual SQL statement "COMMIT" (e.g. via ODBC's SQLPrepare()/SQLExecute())? If so, would it be optimized when using SQLExecDirect()?

[ian_mchardy]

Volker: COMMIT and ROLLBACK are only optimized with SQLEndTran or ODBC autocommits. COMMIT and ROLLBACK are never optimized with SQLPrepare/SQLExecute, SQLExecDirect or any other mechanism that executes SQL text.

[Former Member]

When you run dbtran with "-a", all connections will show a rollback when the connection is logically dropped, either at the end of the log or when the log records that a database was restarted.

You might try enabling request logging on the server to find the cause of your problem. Perhaps an error is being returned by the server on an insert/update that is being ignored/suppressed by the application.