Oracle事务概念

本文参考了官网http://docs.oracle.com/cd/B28359_01/server.111/b28318/transact.htm 必看！

这篇文章包含了一些更细节的描述：http://docs.oracle.com/cd/E25054_01/server.1111/e25789/transact.htm#i974

事务的开始：

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.
When a transaction begins, Oracle Database assigns the transaction to an available undo tablespace to record the rollback entries for the new transaction.用户也可以指定使用的回滚表空间。

事务的结束：
A transaction ends when any of the following occurs:
1, A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause. 这里注意回滚到某个savepoint本身并不会结束当前事务，而是处于挂起的状态。
2, A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements, Oracle Database first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction. 因此DDL不能作为事务的一部分，同时用户也无法控制是否回滚DDL。
3, A user disconnects from Oracle Database. The current transaction is committed.
4, A user process terminates abnormally. The current transaction is rolled back.

Oracle不能像SQL Server那样显式的开始一个事务，也不能嵌套事务。

回滚到Savepoint：

When a transaction is rolled back to a savepoint, the following occurs:
1, Oracle Database rolls back only the statements run after the savepoint.
2, Oracle Database preserves the specified savepoint, but all savepoints that were established after the specified one are lost.
3, Oracle Database releases all table and row locks acquired since that savepoint but retains all data locks acquired previous to the savepoint.
The transaction remains active and can be continued.

因此回滚到savepoint之后，savepoint之后的DML被撤销，savepoint之前的DML不会被撤销，但也没有被commmit，当前事务挂起。因此用户还可以继续输入其他DML以及commit等语句。如果用户没有进一步的行为，则会被事务结束条件的3，4点结束。

如果是存储过程等代码，事务如何结束则和session如何结束相关。总的来说同样适用于事务结束条件的3，4点。

if there is no commit in the procedure, the transaction will be ended by the session that calls it; any locks will be held until the session issues a commit (or rollback). If it doesn't ever explicitly do so then the session will implicitly perform a commit or rollback when it ends, depending on how it is terminated. The transaction may exist before the procedure call to. The procedure is one statement within the transaction.

Statement-Level Rollback

If at any time during execution a SQL statement causes an error, all effects of the statement are rolled back. The effect of the rollback is as if that statement had never been run. This operation is a statement-level rollback.

Errors discovered during SQL statement execution cause statement-level rollbacks. An example of such an error is attempting to insert a duplicate value in a primary key. Single SQL statements involved in a deadlock (competition for the same data) can also cause a statement-level rollback. Errors discovered during SQL statement parsing, such as a syntax error, have not yet been run, so they do not cause a statement-level rollback.

A SQL statement that fails causes the loss only of any work it would have performed itself. It does not cause the loss of any work that preceded it in the current transaction. if the statement raises an unhandled exception, the host environment (such as SQL*Plus) determines what is rolled back.