Why do I get an open transaction when just selecting from a database View?

Question

If I execute a simple select statement in pl/sql developer against a database table, I get a standard set of results back as I would expect.

Recently, I pasted a query from a stored procedure that happened to select from a view, and noticed that a transaction was seemingly left open. This was appraent by the rollback and commit options were available in PL/SQL developer.

A poll of other developers revealed that this seems to affect some but not others, which lead me to suspect PL/SQL Developer settings.

Why on earth would this be the case? The view itelf has a DBLink to another database, but I wouldn't expect this to have any effect.

Any thoughts?

Answer 1

Contrary to your expectation, it looks like the database link is the source of the open transaction. I've noticed behaviour like this before when running SELECT queries on remote tables in PL/SQL Developer.

To quote Tom Kyte (source):

distributed stuff starts a transaction "just in case".

EDIT: 'Any SQL statement starts a transaction in Oracle'? No, it does not, and here's a demonstration of it. This demonstration uses the data dictionary view V$TRANSACTION, which lists the active transactions. This is all running on my local Oracle XE database, which has no users other than me connected to it.

We'll use the following table during this demonstration. It contains only a single column:

SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER(38)

SQL> select count(*) from v$transaction;

  COUNT(1)
----------
         0

No active transactions at the moment. Let's run a SQL query against this table:

SQL> select * from test;

         A
----------
         2

SQL> select count(*) from v$transaction;

  COUNT(1)
----------
         0

Still no active transactions. Now let's do something that will start a transaction:

SQL> insert into test values (1);

1 row created.

SQL> select count(*) from v$transaction;

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

As expected, we now have an active transaction.

SQL> commit;

Commit complete.

SQL> select count(*) from v$transaction;

  COUNT(1)
----------
         0

After committing the transaction, it's no longer active.

Now, let's create a database link. I'm using Oracle XE, and the following creates a database link from my Oracle XE instance back to itself:

SQL> create database link loopback_xe connect to user identified by password using 'XE';

Database link created.

Now let's see what happens when we select from the table over the database link:

SQL> select count(*) from v$transaction;

  COUNT(1)
----------
         0

SQL> select * from test@loopback_xe;

         A
----------
         2
         1

SQL> select count(*) from v$transaction;

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

As you can see, simply selecting from a remote table opens a transaction.

I'm not sure exactly what there is to commit or rollback here, but I have to admit to not knowing the ins and outs of distributed transactions, within which the answer probably lies.

Answer 2

Any SQL Statement starts a transaction in Oracle.

From the manual:

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued. [...] An executable SQL statement is a SQL statement that generates calls to an instance, including DML and DDL statements

Most probably those who are not seing this are running in auto-commit mode where the transaction started by a statement is immediately committed after the statement has finished.

Others have claimed that a SELECT is not DML, but again the manual clearly states:

Data manipulation language (DML) statements query or manipulate data in existing schema objects. They enable you to:

   * Retrieve or fetch data from one or more tables or views (SELECT)
   * Add new rows of data into a table or view (INSERT)
[...]