Telling if a transaction has uncommitted updates

Question

In our application, there's a database update that is committed only after a consequent update is being executed (both using the same transaction, of course). However, we've discovered a rare flow in which the user exits the application before the second update, causing the first to be discarded. I'm looking for a way to recognize this uncommitted update upon exit.

I know problems like this call for redesign, but that not possible. Due to the rarity of the flow and the structure of the app, I'm wondering if there's a way to just check the transaction itself for uncommitted updates.

Question is valid for Oracle and SQLServer. The app is written in PowerBuilder, but it can be extended in various ways (.NET, Win32 etc.) if that matters.

Ingress

Answer 1

In Oracle you can call DBMS_TRANSACTION.local_transaction_id. This will either return a unique identifier of the current transaction, or NULL if no transaction is active.

Share and enjoy.

Denni

Answer 2

This might be helpful

@@TRANCOUNT (Transact-SQL)

Returns the number of active transactions for the current connection.

Anamorphic

I thought that was SQL Server only? – Reunite

Answer 3

If you're on PB11.5 and use a custom transaction object, it's pretty easy to do something that isn't DBMS-dependent. In the SQLPreview event of the transaction object, simply toggle a boolean on when an INSERT, UPDATE or DELETE goes by, then toggle it off when a COMMIT or ROLLBACK goes by.

Actually, it's not that hard to swap in a custom transaction object if you're using SQLCA: Application, Properties, Additional Properties, Variable Types, SQLCA. If you're using a lot of separate database connections with a lot of "CREATE transaction" statements (standard PB search can find this, or PBL Peeper can help you find this with variable number of spaces between the words), then implementing this will be harder, but not impossible.

And, for the sake of completeness, to create a custom transaction object, File / New / PB Object / Standard Class / Transaction. You've got a regular user object where you can define Instance Variables (like the boolean I've suggested) and script events (like the SQLPreview event I've suggested) and functions (you may want to create an interface for this functionality to hide the details in case you want to extend it in the future). Note that SQLPreview isn't available on the Transaction object before 11.5. (For those that think it sounds familiar before 11.5, the DataWindow implements a SQLPreview.)

Good luck,

Terry.

Kaja

I'm not getting the COMMIT or ROLLBACK in the transactions's SQL Preview. PB 12.5, ODBC connection. This really bums me because I made a nifty SQL Spy-like window that shows all the SQL going to the database, and turns a big indicator bar red if there's an uncommitted transaction. – Sherman

Answer 4

In Oracle there is a view V$TRANSACTION which contains a row for each uncommitted transaction. There is no mechanism for seeing the nature of that transaction from outside (unless you have instrumentation built into your code, e.g. by using DBMS_APPLICATION_INFO.SET_MODULE() ). However, the current session could see whether it has uncommitted work like this:

SQL> select t.status
  2  from   v$transaction t
  3         join v$session s
  4         on s.saddr = t.ses_addr
  5  where s.sid = sys_context('userenv', 'sid')
  6  /

STATUS
----------------
ACTIVE

SQL>

If there are no uncommitted transactions then this query will return NO_DATA_FOUND. The V$ views are not granted to users by default, as they are really a DBA thang. However, a user with the appropriate privilege can turn this query into a view and grant access to the regular joes.

As a matter of interest, what would you want to do, anyway? Presuming the Unit Of Work is correctly defined ad two updates, surely it would be wrong to commit just the one. If all you want to know is that this abnormal termination occurred then some form of trace or logging is what you need.

edit

Bob Jarvis proposes using DBMS_TRANSACTION.LOCAL_TRANSACTION_ID(). That is a better suggestion than a handcrafted view. The V$TRANSACTION view can also be used to monitor uncommitted transactions from other sessions.

Risotto

Well, the two updates are not really one unit of work. The uncommitted update is actually a rerouted call to an external server, which instead of being transmitted is stored at the DB. Our app has a consistent behavior with regards to committing, but the update comes from a different part of the app. A separate transaction might have been appropriate, but that too would be a lot of work. So, it should be ok to commit the one uncommitted update. – Ingress

Answer 5

In order to facilitate the troubleshooting of your scenario you may wish to consider the use of explicit named local transactions, along with the use of the “WITH MARK” option. This permits you to record the name of an explicit transaction to the transaction log, which you can of course inspect at a later stage in order to identify the sequence of events that has occurred.

See SQL Server Books Online: Marked Transactions

Apian

Answer 6

In SQL Server, run this:

IF @@TRANCOUNT>0 BEGIN
  ROLLBACK;
END;

Balladry

Answer 7

In SQL Server 2005/2008, you can use the DMV. Check out this article

Morley