How to Tell If I Have Uncommitted Work in an Oracle Transaction

How can I tell if I have uncommitted work in an Oracle transaction?

SELECT  *
FROM    v$session v
WHERE   v.AUDSID = userenv('sessionid')
    AND v.TADDR IS NOT NULL

Oracle: How to find out if there is a transaction pending?

you can check if your session has a row in V$TRANSACTION (obviously that requires read privilege on this view):

SQL> SELECT COUNT(*)
       FROM v$transaction t, v$session s, v$mystat m
      WHERE t.ses_addr = s.saddr
        AND s.sid = m.sid
        AND ROWNUM = 1;

  COUNT(*)
----------
         0

SQL> insert into a values (1);

1 row inserted

SQL> SELECT COUNT(*)
       FROM v$transaction t, v$session s, v$mystat m
      WHERE t.ses_addr = s.saddr
        AND s.sid = m.sid
        AND ROWNUM = 1;

  COUNT(*)
----------
         1

SQL> commit;

Commit complete

SQL> SELECT COUNT(*)
       FROM v$transaction t, v$session s, v$mystat m
      WHERE t.ses_addr = s.saddr
        AND s.sid = m.sid
        AND ROWNUM = 1;

  COUNT(*)
----------
         0

What happens to uncommitted sql statements?

If there is no COMMIT or ROLLBACK statements, then the work will be rolled back when the session ends.

Some detailed explanation you'll find in Transation Management Doc

UPDATE APC is right, the doc is more specific than me:

A transaction ends when any of the following occurs:

A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT
clause.

A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If
the current transaction contains any DML statements, Oracle Database
first commits the transaction, and then runs and commits the DDL
statement as a new, single statement transaction.

A user disconnects from Oracle Database. The current transaction is
committed.

A user process terminates abnormally. The current transaction is
rolled back.

Note: Applications should always explicitly commit or undo transactions before program termination.

So, I didn't really answered to your question. It depends on what your connector does at disconection.

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.

Share and enjoy.

What happens to an uncommitted transaction when the connection is closed?

It can stay open while connection pooling applies. Example: command timeout can leave locks and TXN because the client sends as "abort".

2 solutions:

• Test in the client, literally:

  IF @@TRANCOUNT <> 0 ROLLBACK TRAN

• Use SET XACT_ABORT ON to ensured a TXN is cleaned up: Question 1 and Question 2

I always use SET XACT_ABORT ON.

From this SQL Team blog:

Note that with connection pooling,
simply closing the connection without
a rollback will only return the
connection to the pool and the
transaction will remain open until
later reused or removed from the pool.
This can result in locks begin held
unnecessary and cause other timeouts
and rolling block

From MSDN, section "Transaction Support" (my bold)

When a connection is closed, it is
released back into the pool and into
the appropriate subdivision based on
its transaction context. Therefore,
you can close the connection without
generating an error, even though a
distributed transaction is still
pending. This allows you to commit or
abort the distributed transaction at a
later time.

Does oracle allow the uncommitted read option?

Tom provides a great answer to this: On Transaction Isolation Levels

He says:

The READ UNCOMMITTED
isolation level allows dirty reads.
Oracle Database doesn't use dirty
reads, nor does it even allow them.
The basic goal of a READ UNCOMMITTED
isolation level is to provide a
standards-based definition that allows
for nonblocking reads.

...

Now, a database that allowed a dirty
read ... not only does it return the
wrong answer, but also it returns ... [an answer] ... that never existed in the table.
In a multiuser database, a dirty read
can be a dangerous feature.
Personally, I've never seen the
usefulness of it...

The point here is that dirty read is
not a feature; rather, it's a
liability. In Oracle Database, it's
just not needed. You get all of the
advantages of a dirty read—no
blocking—without any of the incorrect
results.

---

Related Topics