Oracle – Any way to view uncommited changes to a particular table

oracleoracle-10gtransaction

I'm debugging through a batch process currently that does a lot of DML statements, but doesn't do a commit right away. It would be nice to be able to view the "pending" changes from another session while the transaction is not committed. Is this possible?

Example:

Insert into table myTable (col1, col2) values ("col1", "col2");

--Somehow view the pending transaction maybe by system view?....

...other DML statements....

commit;

Best Answer

There are a few different approaches depending on the details of your batch process and why you're trying to view the uncommitted changes.

1) Oracle Workspace Manager is a tool that was originally designed to allow people developing Spatial applications to have the equivalent of extremely long-running transactions (i.e. transactions that may require multiple days or weeks of humans figuring out where to run a pipeline in one transaction). Your batch process could create a new workspace (which is logically like creating a new transaction), make whatever changes it would like in that workspace while committing whenever it wanted. In a separate session, you wouldn't see any of the committed changes until you entered the batch process's workspace. When the batch process finished, it could merge it's workspace back into the live workspace which is the equivalent of committing a transaction.

2) The DBMS_XA package can be used to allow you to "hand off" a transaction from one session to another and to allow one session to connect to a transaction started by another session. This is a pretty obscure package to be using, but there was a nice example of using it in the PL/SQL Challenge (you may need a free account to access it) recently.

3) If you're just trying to see the status of the batch process rather than seeing the actual data, the batch process can write logging information using autonomous transactions that you could then query from another session. Or you could use the DBMS_APPLICATION_INFO package to have your application update various attributes in V$SESSION and/or V$SESSION_LONGOPS so that you could monitor the status of the load from another session.

Related Solutions

Best Isolation Level for Non-Blocking Select Statements in SQL Server

In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.

This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.

Oracle 11g hunting down the deadlock, maybe foreign key

Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys:

There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.

Locks in Oracle are managed at the row level. Concurrent disjoint transactions should not interfere with one another. Unindexed foreign keys are an exception, since it can result in a complete TABLE LOCK.

You should get the SQL in the trace file of the deadlock and that should help you narrow down which table / foreign key is responsible for the lock. Once you know which table is affected by the deadlock, make sure that all foreign key references to this table are properly indexed. E.G in your example T2.t1_id should be indexed if it points to T1.t1_id.

Alternatively you could use Tom Kyte's script from the above link to determine if you have any unindexed foreign key.

If you're locked on inserts, it usually means that you're trying to insert rows that have the same values for a set of columns that are UNIQUE, for example:

CREATE TABLE T1 (col1 NUMBER NOT NULL, col2 NUMBER);
ALTER TABLE T1 ADD CONSTRAINT T1_UNIQUE UNIQUE (col1, col2);

session1> INSERT INTO T1 VALUES (1, NULL);
session2> INSERT INTO T1 VALUES (2, NULL);
session2> INSERT INTO T1 VALUES (1, NULL); -- <= this will wait on session1
session1> INSERT INTO T1 VALUES (2, NULL); -- <= Deadlock !

Best Answer

Related Solutions

Best Isolation Level for Non-Blocking Select Statements in SQL Server

Oracle 11g hunting down the deadlock, maybe foreign key

Related Question