为什么仅从数据库视图中进行选择时为什么会得到未结交易? [英] Why do I get an open transaction when just selecting from a database View?

问题描述

如果我在pl/sql开发人员中针对数据库表执行一个简单的select语句，则会得到我期望的一组标准结果.

最近，我从一个存储过程中粘贴了一个查询，该查询恰巧是从视图中选择的，并注意到事务似乎处于打开状态.回滚和批处理选项对此都具有影响力.PL/SQL开发人员中提供了该选项.

对其他开发人员的民意测验表明，这似乎影响了一些人，但没有影响其他人，这使我怀疑PL/SQL Developer的设置.

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

有什么想法吗?

解决方案

任何 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屋！