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:
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.
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.
answered 21 Mar '11, 12:35
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.