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?

Luke Woodward · Answer 1 · 2010-12-20T12:44:37.103

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.

From the Oracle concepts manual: "A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back". http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/transact.htm#i1666 — , Dec 20 '10 at 12:47
You're right, but just FYI you need to be careful with querying from v$transaction, at least on XE. Some transactions don't appear in that view. For example, "update a set test = 0 where 1 = 2" does not always create rows in v$transaction, but does show up in dbms_transaction.local_transaction_id(). — Jon Heller, Dec 21 '10 at 03:32
@Luke_Woodward A query over a db link within Oracle opens a transaction in order to support 2 phase commits. You can query the DBA_2PC_PENDING table to see the entries on the remote db. When querying over the db link, I don't think the initiator can know what the side effects are on the remote db. You may be querying from a function and that function may have side effects modifying data. My experience (8i timeframe): a failure on the initiating db after the query, but before a commit or rollback, left the transaction open on the remote db, locking the row, failing other jobs. Painful to find. — Glenn, Apr 05 '12 at 01:58
@JonHeller Apparently it will appear in `v$transaction` after the call to `dbms_transaction.local_transaction_id` has been made (or another DML statement which is not `SELECT`) . It is a little bit weird. Cfr. https://stackoverflow.com/a/17706031/452614 — Ludovic Kuty, Feb 24 '19 at 06:37

score 3 · Answer 2 · edited Apr 05 '12 at 13:43

3

You absolutely cannot open a transaction strictly with a normal query. You may open one across a database link. The guy who posted a link to the doctors either deliberately or utterly carelessly left out the 2nd sentence.

"A transaction in Oracle Database begins when the first executable SQL statement is encountered. An executable SQL statement is a SQL statement that generates calls to an instance, including DML and DDL statements."

SELECT is neither a DML nor a DDL. It is also TRIVIAL to actually test this. I don't want to come off like a troll here, but its really annoying when people just throw out answers on a forum to try to get points and the answers are complete garbage.

Read the rest of the doc and TEST IT FIRST.

login to a session
run a select
see if you have an open transaction by joining v$Session (for your session) to v$transaction.

If a record comes back, you have a transaction. If not, you don't.

edited Apr 05 '12 at 13:43

Michael J. Barber

24,518
9
68
88

answered Apr 04 '12 at 16:02

Bob

2,506
4
21
18

4

"*SELECT is neither DML nor DDL*". The manual begs to differ: http://docs.oracle.com/cd/B28359_01/server.111/b28318/sqlplsql.htm#autoId2 `SELECT` is clearly categorized as a DML statement there. – Oct 31 '13 at 06:56
1

Sorry, @a_horse_with_no_name but the 11g documentation does explicitly state in [SET TRANSACTION](http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10005.htm) that just executing a regular SELECT (without FOR UPDATE) doesn't start a transaction. And as people are demonstrating - and I just learned the hard way today - that seems like the real behavior. – Ross Bradbury Oct 20 '14 at 21:48
IMHO The documentation is self contradictory. It looks like writing that DML statements start a transaction is not correct since `SELECT` in `READ COMMITTED` does not start a new transaction but it qualifies as DML. – Ludovic Kuty Feb 24 '19 at 07:51

score 3 · Accepted Answer · 2013-10-31T06:58:55.877

3

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)
[...]

edited Oct 31 '13 at 06:58

answered Dec 20 '10 at 10:55

Thanks. How is the Auto-Commit mode toggled? – James Wiseman Dec 20 '10 at 10:57
I don't use PL/SQL Developer, so I don't know – Dec 20 '10 at 11:00
6

@a_horse_with_no_name: not every SQL statement starts a transaction. See the demonstration in my (updated) answer. – Luke Woodward Dec 20 '10 at 12:46
1

@Pourquoi: See the Oracle concepts manual: "A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back". Why this is not reflected in V$TRANSACTION I cannot tell. http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/transact.htm#i1666 – Dec 20 '10 at 12:53
@a_horse_with_no_name: hmmm.... I think the concepts manual may be a bit ambiguous here. I suspect they don't mean to include SELECT when they say 'SQL statements'. I'd guess PL/SQL Developer uses V$TRANSACTION to detect active transactions, and I don't know any way of detecting active transactions without using this view one way or another, hence my answer. See also http://www.freelists.org/post/oracle-l/Does-SELECT-start-a-transaction%2c1 , http://stackoverflow.com/questions/1299694/ and http://stackoverflow.com/questions/506456/#509032 – Luke Woodward Dec 20 '10 at 13:26
2

@Porquoi: Hmm... Oracle needs to have "some kind" of transaction to be able to support read-consistency. When setting the session to serializable V$TRANSACTION does in fact show an entry for a simple SELECT statement. So maybe the "transaction" for a SELECT in READ COMMITTED mode is so "weak" it is not recorded in V$TRANSACTION – Dec 20 '10 at 13:41
1

I think v$transaction tracks a start SCN. For a SERIALIZABLE transaction this does need to include SELECTs but otherwise it should just need to be the point the transaction may need to rollback to if it ever needs to be fully rolled back. Database links need a commit/rollback to close, so maybe that's why they need a v$transaction entry. – Gary Myers Dec 20 '10 at 21:50
1

@a_horse_with_no_name: I don't believe a transaction is necessary for a SELECT in READ COMMITTED mode. To achieve read consistency, Oracle just makes a note of the time it started running the SELECT query and goes back into the undo if it comes across any data newer than this. See http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:27330770500351 – Luke Woodward Dec 20 '10 at 21:56

score 1 · Answer 4 · answered Jan 20 '15 at 19:20

1

Note, according to the Oracle 11g Admin Guide, if you do a plain old SELECT across a database link you will start a transaction, which needs to be committed (or rolled back).

answered Jan 20 '15 at 19:20

eze

2,332
3
19
30

score 0 · Answer 5 · answered Feb 20 '19 at 13:45

Select is a part of DML only but lock is not acquired, A row lock is fetched upon insert/update/delete/ select for update. -Ross is right.

https://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502

                        ROW Lock    Table lock

SELECT ... FROM table... ----- None

INSERT INTO table ... Yes SX

Same with update delete and select for update.

score 0 · Answer 6 · answered May 27 '20 at 11:35

TLDR : On select from remote database you also create session and connection for remote DB. That session and connection persists as long as local user session. As you can guess this can lead to some problems with keeping up with session and connections.

SO ALWAYS DO A COMMIT : SELECT * FROM emp@sales; COMMIT;

I like a long read section :

This was bugging me also so much why there is an transaction on selects from db_links and decided to finally end this so from oracle documentation :

Oracle® Database Administrator's Guide 11g Release 2 (11.2) https://docs.oracle.com/html/E25494_01/ds_appdev002.htm

Controlling Connections Established by Database Links When a global object name is referenced in a SQL statement or remote procedure call, database links establish a connection to a session in the remote database on behalf of the local user. The remote connection and session are only created if the connection has not already been established previously for the local user session.

The connections and sessions established to remote databases persist for the duration of the local user's session, unless the application or user explicitly terminates them. Note that when you issue a SELECT statement across a database link, a transaction lock is placed on the undo segments. To rerelease the segment, you must issue a COMMIT or ROLLBACK statement.

Terminating remote connections established using database links is useful for disconnecting high cost connections that are no longer required by the application. You can terminate a remote connection and session using the ALTER SESSION statement with the CLOSE DATABASE LINK clause. For example, assume you issue the following transactions:

SELECT * FROM emp@sales; COMMIT; The following statement terminates the session in the remote database pointed to by the sales database link:

ALTER SESSION CLOSE DATABASE LINK sales; To close a database link connection in your user session, you must have the ALTER SESSION system privilege.

Note: Before closing a database link, first close all cursors that use the link and then end your current transaction if it uses the link.

See Also: Oracle Database SQL Language Reference for more information about the ALTER SESSION statement

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

6 Answers6

Linked