oracle 为什么只从数据库视图中选择时会得到一个打开的事务？

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

提问by James Wiseman

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.

如果我在 pl/sql developer 中对数据库表执行一个简单的 select 语句，我会得到一组标准的结果，正如我所期望的那样。

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.

最近，我粘贴了一个来自存储过程的查询，该查询碰巧从视图中选择，并注意到一个事务似乎保持打开状态。这是明显的回滚和提交选项在 PL/SQL 开发人员中可用。

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

对其他开发人员的民意调查显示，这似乎影响了一些但不影响其他开发人员，这让我怀疑 PL/SQL Developer 设置。

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.

为什么会是这种情况？视图 itelf 有一个到另一个数据库的 DBLink，但我不希望这有任何影响。

Any thoughts?

有什么想法吗？

采纳答案by a_horse_with_no_name

AnySQL Statement starts a transaction in Oracle.

任何SQL 语句都会在 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 executableSQL statement is a SQL statement that generates calls to an instance, including DML and DDL statements

事务从第一个可执行的 SQL 语句开始。事务在提交或回滚时结束，无论是使用 COMMIT 或 ROLLBACK 语句显式还是在发出 DDL 语句时隐式。[...]可执行SQL 语句是生成对实例的调用的 SQL 语句，包括 DML 和 DDL 语句

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 SELECTis not DML, but again the manual clearly states:

其他人声称 aSELECT不是 DML，但手册再次明确指出：

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

数据操作语言 (DML) 语句查询或操作现有模式对象中的数据。它们使您能够：

   * 从一个或多个表或视图中检索或获取数据 (SELECT)
   * 将新数据行添加到表或视图中 (INSERT)
[...]

回答by Luke Woodward

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

与您的预期相反，看起来数据库链接是打开事务的来源。我以前在 PL/SQL Developer 中对远程表运行 SELECT 查询时注意到过这样的行为。

To quote Tom Kyte (source):

引用 Tom Kyte（来源）：

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.

编辑：'任何 SQL 语句在 Oracle 中启动一个事务'？不，它没有，这里有一个演示。此演示使用数据字典视图V$TRANSACTION，其中列出了活动事务。这一切都在我的本地 Oracle XE 数据库上运行，除了我之外没有其他用户连接到它。

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 查询：

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:

现在，让我们创建一个数据库链接。我正在使用 Oracle XE，以下创建了一个从我的 Oracle XE 实例返回到自身的数据库链接：

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.

我不确定这里到底有什么要提交或回滚，但我不得不承认我不知道分布式事务的来龙去脉，答案可能就在其中。

回答by Bob

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."

“当遇到第一个可执行 SQL 语句时，Oracle 数据库中的事务就开始了。可执行 SQL 语句是生成对实例的调用的 SQL 语句，包括 DML 和 DDL 语句。”

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.

SELECT 既不是 DML 也不是 DDL。实际测试这个也是微不足道的。我不想在这里像个巨魔一样离开，但是当人们只是在论坛上抛出答案以试图获得积分而答案完全是垃圾时，这真的很烦人。

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.

• 登录会话
• 运行选择
• 通过加入v$Session（为您的会话）到v$transaction.

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

如果记录回来，您就有了交易。如果没有，你就没有。

回答by eze

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

请注意，根据Oracle 11g 管理指南，如果您在数据库链接上执行普通的旧 SELECT，您将启动一个需要提交（或回滚）的事务。

回答by Sanjay K

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.

Select 只是 DML 的一部分，但不获取锁，在插入/更新/删除/选择更新时获取行锁。- 罗斯是对的。

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

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

                        ROW Lock    Table lock

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

SELECT ... FROM table... ----- 无

INSERT INTO table ... Yes SX

INSERT INTO 表 ... 是 SX

Same with update delete and select for update.

与更新删除和选择更新相同。

回答by Peter

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.

TLDR：从远程数据库中选择时，您还可以为远程数据库创建会话和连接。只要本地用户会话，该会话和连接就会持续存在。正如您所猜测的那样，这可能会导致与会话和连接保持同步的一些问题。

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

所以总是做一个承诺：SELECT * FROM emp@sales; 犯罪;

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 :

这也让我很烦恼，为什么从 db_links 中选择有一个事务，并决定最终从 oracle 文档中结束它：

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

甲骨文？数据库管理员指南 11g 第 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.

控制由数据库链接建立的连接 当在 SQL 语句或远程过程调用中引用全局对象名称时，数据库链接代表本地用户建立到远程数据库中的会话的连接。仅当之前尚未为本地用户会话建立连接时，才会创建远程连接和会话。

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.

与远程数据库建立的连接和会话在本地用户会话期间持续存在，除非应用程序或用户明确终止它们。请注意，当您跨数据库链接发出 SELECT 语句时，事务锁会放置在撤消段上。要重新释放段，您必须发出 COMMIT 或 ROLLBACK 语句。

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:

终止使用数据库链接建立的远程连接对于断开应用程序不再需要的高成本连接非常有用。您可以使用带有 CLOSE DATABASE LINK 子句的 ALTER SESSION 语句终止远程连接和会话。例如，假设您发出以下交易：

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

SELECT * FROM emp@sales; 犯罪; 以下语句终止 sales 数据库链接指向的远程数据库中的会话：

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

ALTER SESSION CLOSE DATABASE LINK 销售；要关闭用户会话中的数据库链接连接，您必须具有 ALTER SESSION 系统权限。

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

另请参阅：Oracle 数据库 SQL 语言参考，了解有关 ALTER SESSION 语句的更多信息