为什么只从数据库视图中选择时会得到一个打开的事务? [英] Why do I get an open transaction when just selecting from a database View?

问题描述

如果我在 pl/sql developer 中对一个数据库表执行一个简单的 select 语句，我会按照我的预期得到一组标准的结果.

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

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

到底为什么会这样?视图 itelf 有一个到另一个数据库的 DBLink，但我不希望这有任何影响.

有什么想法吗?

解决方案

Any SQL 语句在 Oracle 中启动一个事务.

来自手册:><块引用>

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

那些没有看到这一点的人很可能在自动提交模式下运行，在该模式下，由语句启动的事务在语句完成后立即提交.

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

<块引用>数据操作语言 (DML) 语句查询或操作现有模式对象中的数据.它们使您能够:

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

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?

解决方案

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

这篇关于为什么只从数据库视图中选择时会得到一个打开的事务?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！