Menu
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.
389
Answers. You would need to query V$SESSION for your session to see the sql the session has used. Or you could use the sql monitor that is part of sql developer. Alternatively, if you have access to the SYS or SYSTEM users, or privileges on the V$ views, you could also try something like...
The easiest way is to check if there are any transactions is select * from gv$transaction. This query shows any open transactions in your Oracle system.
Two things to check. One is that you might have the "implicit transaction" setting turned on, which means EVERYTHING it wrapped in a transaction. Check the properties of the server/database. Otherwise, you can use the DBCC OPENTRAN function to find any uncommitted transactions....
As long as you don't COMMIT or ROLLBACK a transaction, it's still "running" and potentially holding locks. If your client (application or user) closes the connection to the database before committing, any still running transactions will be rolled back and terminated.
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.
192
This might be helpful
@@TRANCOUNT (Transact-SQL)
Returns the number of active transactions for the current connection.
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
