Telling if a transaction has uncommitted updates?

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.

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.

This might be helpful @@TRANCOUNT (Transact-SQL) Returns the number of active transactions for the current connection.

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.

That's good to know. Thanks, Terry! – eran Dec 29 '09 at 19:13.

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.

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. – eran Dec 22 '09 at 11:33.

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.

In SQL Server, run this: IF @@TRANCOUNT>0 BEGIN ROLLBACK; END.

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

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.