SQL基础15——数据库事务(TRANSACTION)

一、数据库事务

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

   

二、数据库事务的ACID属性

    原子性（atomic）

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

 

    一致性（consistent）

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

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

        表）都必须是正确的。

 

    隔离性（insulation）

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

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

        间状态的数据。

 

    持久性（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时才对彼此的查询产生影响

   

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

SQL INSERT INTO emp(empno,ename,salary)

  2  SELECT 6666,Jenney,3000 FROM DUAL;

 

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

 

 

 

八、利用AUTOCOMMIT进行事务控制

    SET AUTOCOMMIT ON

   

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

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

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

 

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

SQL基础-- 数据库事务(TRANSACTION) --====================== --SQL基础-- 数据库事务 --======================   一、数据库事务   数据库事务是指作为单个逻辑工作单元执行的一系列操作，可以认为事务就是一组不可分割的SQL语句   二、数据库事务的ACID属性   原子性（atomic）   事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
第十二届 BigData NoSQL Meetup — 基于hbase的New sql落地实践 立即下载