SQL基础-->数据库事务(TRANSACTION)

--======================

--SQL基础-->数据库事务

--======================

一、数据库事务

    数据库事务是指作为单个逻辑工作单元执行的一系列操作，可以认为事务就是一组不可分割的SQL语句

二、数据库事务的ACID属性

    原子性（atomic）

　　     事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

    一致性（consistent）

　　     事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务

        的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如B 树索引或双向链

        表）都必须是正确的。

    隔离性（Isolation）

　　     由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状

        态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中

        间状态的数据。(注:更正@20131218)

    持久性（Duration）

　　     事务完成之后，它对于系统的影响是永久性的。

三、事务的组成

    一个数据库事务可由：

       一个或多个DML语句组成

       一个DDL语句组成

       一个DCL语句组成

    一个数据事务由：

       以第一个DML语句的执行作为开始

       以下面的其中之一作为结束

           commit     （提交）

           rollback   （回滚）

           ddl或dcl语句   （自动提交）

           用户会话正常结束（自动提交） 

           系统异常终止 （自动回滚）

           系统崩溃 （自动回滚）

四、事务的提交或回滚(COMMIT、ROLLBACK)       

    --使用COMMIT事务，robinson记录被插入到表

       SQL> INSERT INTO scott.emp(empno,ename,job,salary)

         2  VALUES(9999,'Robinson','DBA',3500);

       1 row created.

       SQL> COMMIT;

       Commit complete.

       SQL> UPDATE scott.emp SET salary = 4000 WHERE ename = 'Robinson';

       1 row updated.

    --使用ROLLBACK回滚，更新将失效，最终结果如下面的查询

       SQL> ROLLBACK;

       Rollback complete.

       SQL> SELECT * FROM scott.emp WHERE ename = 'Robinson';

            EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO

       ---------- ------------------------------ --------- ---------- --------- ---------- ----------

             9999 Robinson                       DBA                                  3500 

五、保存点

    设置保存点：

       SAVEPOINT NAME

    恢复至保存点：

       ROLLBACK TO NAME

    --查看empno为，的记录  

       SQL> SELECT * FROM scott.emp WHERE empno IN (1235,1236);

            EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO

       ---------- ------------------------------ --------- ---------- --------- ---------- ----------

             1235 Tony                           boy                  28-JUN-10       7100

             1236 Ben                            IT                   28-JUN-10       3100         20

    --首先更新empno 为的salary ，并设置了一个保存点tran1     

       SQL> UPDATE scott.emp SET salary = salary * 1.5 WHERE empno = 1236;

       1 row updated.

       SQL> SAVEPOINT tran1;

       Savepoint created.

    --更新empno 为的salary

       SQL> UPDATE scott.emp SET salary = salary * 0.8 WHERE empno = 1235;

       1 row updated.

    --查看刚刚更新的两条记录

       SQL> SELECT * FROM scott.emp WHERE empno IN (1235,1236);

            EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO

       ---------- ------------------------------ --------- ---------- --------- ---------- ----------

             1235 Tony                           boy                  28-JUN-10       5680

             1236 Ben                            IT                   28-JUN-10       4650         20

    --将事务回滚到保存点tran1

       SQL> ROLLBACK TO SAVEPOINT tran1;

       Rollback complete.

    --可以看到保存点之后的修改被回滚，而保存点之前的修改则不受影响

       SQL> SELECT * FROM scott.emp WHERE empno IN (1235,1236);

            EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO

       ---------- ------------------------------ --------- ---------- --------- ---------- ----------

             1235 Tony                           boy                  28-JUN-10       7100

             1236 Ben                            IT                   28-JUN-10       4650         20

    --对所作的修改全部ROLLBACK，此时仅仅包含了empno 为的记录,因为已经被回滚

       SQL> ROLLBACK;

       Rollback complete.

       SQL>  SELECT * FROM scott.emp WHERE empno IN (1235,1236);

            EMPNO ENAME                          JOB              MGR HIREDATE      SALARY     DEPTNO

       ---------- ------------------------------ --------- ---------- --------- ---------- ----------

             1235 Tony                           boy                  28-JUN-10       7100

             1236 Ben                            IT                   28-JUN-10       3100         20

六、事务的开始与结束及不同时刻的状态

    事务的开始

       连接到数据并执行了一条DML语句(INSERT ,UPDATE,DELETE)

       前一条事务结束后，又执行了另外一条DML语句

    事务的结束

       执行COMMIT 或ROLLBACK

       执行DDL语句，则自动提交并结束事务

       执行DCL语句，则自动提交并结束事务

       断开与数据库的连接，如退出SQL Plus。

           Windows下SQL Plus正常退出将执行COMMIT，如点击关闭窗口直接关闭则回滚

           X-Window下SQL Plus正常退出将执行COMMIT，如点击关闭窗口直接关闭则回滚(笔者在RHEL5下测试如此)

       DML语句执行失败则自动回滚

    提交或回滚前的数据状态

       改变前的数据状态是可以恢复的

       执行DML 操作的用户可以通过SELECT 语句查询之前的修正

       其他用户不能看到当前用户所做的改变，直到当前用户结束事务。

       DML语句所涉及到的行被锁定，其他用户不能操作

    提交后的数据状态

       数据的改变已经被保存到数据库中。

       改变前的数据已经丢失。

       所有用户可以看到结果。

       锁被释放，其他用户可以操作涉及到的数据。

       所有保存点被释放。

七、并发事务

    多个用户同时与数据库交互，且每个用户都可以同时访问自己的事物，这种事务称为并发事务

    对于同一个对象上运行的多个事务，仅当执行commit时才对彼此的查询产生影响

下表中演示了并发事务的处理：

    示例中显示了两个不同的事务交叉执行的顺序，可以看出，仅当事务执行COMMIT后，相关事务才产生影响

八、利用AUTOCOMMIT进行事务控制

    SET AUTOCOMMIT ON

    设置自动提交，每执行一条语句，就提交。将autocommit设成ON时，在进行DML操作时似乎很方便，

    但在实际应用中有时可能会出现问题，如，在有些应用中要同时对几个表进行操作，对于这些表建立

    了外键联系，如果一旦操作失败另一个表，就很麻烦了。

       关于锁及事物的隔离级别请关注后续文章

九、更多

Oracle 参数文件

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

Oracle 用户、对象权限、系统权限

Oracle 角色、配置文件

SQL 基础--> 集合运算(UNION 与UNION ALL)