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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

长烟慢慢 2011-12-26 13:28:48 浏览600
展开阅读全文

一、数据库事务

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

   

二、数据库事务的ACID属性

    原子性(atomic)

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

 

    一致性(consistent)

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

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

        表)都必须是正确的。

 

    隔离性(insulation)

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

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

        间状态的数据。

 

    持久性(Duration)

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

 

三、事务的组成

    一个数据库事务可由:

       一个或多个DML语句组成

       一个DDL语句组成

       一个DCL语句组成

 

    一个数据事务由:

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

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

           commit     (提交)

           rollback   (回滚)

           ddl或dcl语句   (自动提交)

           用户会话正常结束(自动提交) 

           系统异常终止 (自动回滚)

           系统崩溃 (自动回滚)

 

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

    --使用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时才对彼此的查询产生影响

   

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

 

Trans1

Trans2

T1

SQL> SELECT COUNT(1) FROM emp;

 

  COUNT(1)

----------

        16

SQL> SELECT COUNT(1) FROM scott.emp;

 

  COUNT(1)

----------

        16

T2

SQL> INSERT INTO emp(empno,ename,salary)

  2  SELECT 6666,'Jenney',3000 FROM DUAL;

 

T3

SQL> UPDATE emp

  2  SET salary = salary + 200 WHERE ename = 'SCOTT';

 

T4

SQL> SELECT COUNT(1) FROM emp;

 

  COUNT(1)

----------

        17

SQL> SELECT COUNT(1) FROM scott.emp;

 

  COUNT(1)

----------

        16

T5

SQL> COMMIT;

 

T6

 

SQL> SELECT COUNT(1) FROM scott.emp;

 

  COUNT(1)

----------

        17

 

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

 

 

 

八、利用AUTOCOMMIT进行事务控制

    SET AUTOCOMMIT ON

   

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

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

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

 

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


网友评论

登录后评论
0/500
评论