oracle 事务

 

http://blog.chinaunix.net/u/19782/showart_205139.html

一个事务包含一个或多个SQL语句，是逻辑管理的工作单元（原子单元）。
一个事务开始于第一次执行的SQL语句，结束于Commit 或 Rollback 或 DDL语句。
    注意：其中Commit, Rollback是显示的提交事务，而DDL语句是隐式的提交事务的。DDL语句的操作是

SQL> create table a ( i int);

表已创建。

SQL> insert into a values(1);

已创建 1 行。

SQL> create table b ( i int);

表已创建。

SQL> rollback;

回退已完成。

SQL> select * from a;

         I                                                                      
----------                                                                      
         1
#############################
在执行create table b 的时候事务就已经提交了。
事务结束的地方有：
  1>. 执行Commit, Rollback， 没有使用savepoint.
  2>. 执行DDL操作如：create , drop, rename, alter
  3>. 断开与Oracle的连接，事务将自动提交。
  4>. 用户进程异常终止，当前事务回滚。

 

 

注意：应用程序与oracle连接的情况下，在应用程序终止前必须显示的提交和回滚

 

Commit操作Oracle做了：
  1>. 与UNDO表空间关联的内部事务表记录该事务已经提交，产生唯一的系统交易号（SCN）保存到该表

中。
  2>. LGWR进程将SGA中的重做日志写入redo log文件，当然也要写SCN到重做日志文件。
  3>. Oracle释放锁定表中的行。
  4>. Oracle设置该事务完成。

  注意：Commit操作前的改变数据（保存在SGA）不会马上写到数据文件中。这样做的目的也是为了数据

 

oracle事务系统理解

数据库事务的概念
事务是由相关操作构成的一个完整的操作单元。两次连续成功的COMMIT或ROLLBACK之间的操作，称为一个事务。在一个事务内，数据的修改一起提交或撤销，如果发生故障或系统错误，整个事务也会自动撤销。
比如，我们去银行转账，操作可以分为下面两个环节：
(1) 从第一个账户划出款项。
(2) 将款项存入第二个账户。
整个交易过程，可以看作是一个事物，成功则全部成功，失败则需要全部撤消，这样可以避免当操作的中间环节出现问题时，产生数据不一致的问题。
数据库事务是一个逻辑上的划分，有的时候并不是很明显，它可以是一个操作步骤，也可以是多个操作步骤。
我们可以这样理解数据库事物：对数据库所做的一系列修改，在修改过程中，暂时不写入数据库，而是缓存起来，用户在自己的终端可以预览变化，直到全部修改完成，并经过检查确认无误后，一次性提交并写入数据库，在提交之前，必要的话所做的修改都可以取消。提交之后，就不能撤销，提交成功后其他用户才可以通过查询浏览数据的变化。
以事务的方式对数据库进行访问，有如下的优点：
* 把逻辑相关的操作分成了一个组。
* 在数据永久改变前，可以预览数据变化。
* 能够保证数据的读一致性。

数据库事务的应用
数据库事务处理可分为隐式和显式两种。显式事务操作通过命令实现，隐式事务由系统自动完成提交或撤销(回退)工作，无需用户的干预。
隐式提交的情况包括：当用户正常退出SQL*Plus或执行CREATE、DROP、GRANT、REVOKE等命令时会发生事务的自动提交。

还有一种情况，如果把系统的环境变量AUTOCOMMIT设置为ON(默认状态为OFF)，则每当执行一条INSERT、DELETE或UPDATE命令对数据进行修改后，就会马上自动提交。设置命令格式如下：
SET AUTOCOMMIT ON/OFF
隐式回退的情况包括：当异常结束SQL*Plus或系统故障发生时，会发生事务的自动回退。
显式事务处理的数据库事务操作语句有3条，
COMMIT:数据库事务提交，将变化写入数据库
ROLLBACK:数据库事务回退，撤销对数据的修改
SAVEPOINT:创建保存点，用于事务的阶段回退

COMMIT操作把多个步骤对数据库的修改，一次性地永久写入数据库，代表数据库事务的成功执行。ROLLBACK操作在发生问题时，把对数据库已经作出的修改撤消，回退到修改前的状态。在操作过程中，一旦发生问题，如果还没有提交操作，则随时可以使用ROLLBACK来撤消前面的操作。SAVEPOINT则用于在事务中间建立一些保存点，ROLLBACK可以使操作回退到这些点上边，而不必撤销全部的操作。一旦COMMIT完成，就不能用ROLLBACK来取消已经提交的操作。一旦ROLLBACK完成，被撤消的操作要重做，必须重新执行相关操作语句。

如何开始一个新的事务呢？一般情况下，开始一个会话(即连接数据库)，执行第一条SQL语句将开始一个新的事务，或执行COMMIT提交或ROLLBACK撤销事务，也标志新的事务的开始。另外，执行DDL(如CREATE)或DCL命令也将自动提交前一个事务而开始一个新的事务。

数据在修改的时候会对记录进行锁定，其他会话不能对锁定的记录进行修改或加锁，只有当前会话提交或撤销后，记录的锁定才会释放。

观察数据的读一致性
步骤1：显示刚插入的雇员小马:

Sql代码

1. SELECT empno,ename FROM emp WHERE empno=3000;  

SELECT empno,ename FROM emp WHERE empno=3000;

执行结果：
    

Sql代码

1. EMPNO ENAME   
2.         ------------------ -----------   
3.             3000   小马  

EMPNO ENAME

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

          3000   小马

步骤2：删除雇员小马:

Sql代码

1. DELETE FROM emp WHERE empno=3000;  

DELETE FROM emp WHERE empno=3000;

执行结果：
已删除 1 行。
步骤3：再次显示该雇员，显示结果为该雇员不存在：

Sql代码

1. SELECT empno,ename FROM emp WHERE empno=3000;  

SELECT empno,ename FROM emp WHERE empno=3000;

执行结果：
未选定行
步骤4：另外启动第2个SQL*Plus，并以SCOTT身份连接。执行以下命令，结果为该记录依旧存在。

Sql代码

1. SELECT empno,ename FROM emp WHERE empno=3000;  

SELECT empno,ename FROM emp WHERE empno=3000;

执行结果：
    

Sql代码

1. EMPNO ENAME   
2.         ------------------ -----------   
3.               3000   小马  

EMPNO ENAME

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

         3000   小马

步骤5：在第1个SQL*Plus中提交删除：

Sql代码

1. COMMIT;  

COMMIT;

执行结果：
提交完成。
步骤6：在第2个SQL*Plus中再次显示该雇员，显示结果与步骤3的结果一致:

Sql代码

1. SELECT empno,ename FROM emp WHERE empno=3000;  

SELECT empno,ename FROM emp WHERE empno=3000;

执行结果：
未选定行 
说明：在以上训练中，当第1个SQL*Plus会话删除小马后，第2个SQL*Plus会话仍然可以看到该雇员，直到第1个SQL*Plus会话提交该删除操作后，两个会话看到的才是一致的数据。

锁的概念
锁出现在数据共享的场合，用来保证数据的一致性。当多个会话同时修改一个表时，需要对数据进行相应的锁定。
锁有“只读锁”、“排它锁”，“共享排它锁”等多种类型，而且每种类型又有“行级锁”(一次锁住一条记录)，“页级锁”(一次锁住一页，即数据库中存储记录的最小可分配单元)，“表级锁”(锁住整个表)。

排它锁和共享锁

排它锁又称为写锁（(Exclusive lock,简记为X锁)），若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作（INSERT、UPDATE 或 DELETE）过程中始终应用排它锁。
     共享锁又称为读锁（Share lock,简记为S锁），若事务T对数据对象A加上S锁，则其它事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。
     共享锁：由非更新（读取）操作创建的锁。其他用户可以并发读取数据，但任何事务都不能获取数据上的排它锁，直到已释放所有共享锁。 

若为“行级排它锁”，则除被锁住的行外，该表中其他行均可被其他的用户进行修改(Update)或删除(delete)。若为“表级排它锁”，则所有其他用户只能对该表进行查询(select)操作，而无法对其中的任何记录进行修改或删除。当程序对所做的修改进行提交(commit)或回滚(rollback)后，锁住的资源便会得到释放，从而允许其他用户进行操作。
有时，由于程序的原因，锁住资源后长时间未对其工作进行提交；或是由于用户的原因，调出需要修改的数据后，未及时修改并提交，而是放置于一旁；或是由于客户服务器方式中客户端出现“死机”，而服务器端却并未检测到，从而造成锁定的资源未被及时释放，影响到其他用户的操作。
如果两个事务，分别锁定一部分数据，而都在等待对方释放锁才能完成事务操作，这种情况下就会发生死锁。

隐式锁和显式锁
在Oracle数据库中，修改数据操作时需要一个隐式的独占锁，以锁定修改的行，直到修改被提交或撤销为止。如果一个会话锁定了数据，那么第二个会话要想对数据进行修改，只能等到第一个会话对修改使用COMMIT命令进行提交或使用ROLLBACK命令进行回滚撤销后，才开始执行。因此应养成一个良好的习惯：执行修改操作后，要尽早地提交或撤销，以免影响其他会话对数据的修改。

对emp表的SCOTT雇员记录进行修改，测试隐式锁。
步骤1：启动第一个SQL*Plus，以SCOTT账户登录数据库(第一个会话)，修改SCOTT记录，隐式加锁。

Sql代码

1. UPDATE emp SET sal=3500 where empno=7788;  

UPDATE emp SET sal=3500 where empno=7788;

执行结果：
已更新 1 行。
步骤2：启动第二个SQL*Plus，以SCOTT账户登录数据库(第二个会话)，进行记录修改操作。

Sql代码

1. UPDATE emp SET sal=4000 where empno=7788;  

UPDATE emp SET sal=4000 where empno=7788;

执行结果，没有任何输出(处于等待解锁状态)。

步骤3：对第一个会话进行解锁操作：

Sql代码

1. COMMIT;  

COMMIT;

步骤4：查看第二个会话，此时有输出结果：
已更新 1 行。
步骤5：提交第二个会话，防止长时间锁定。
说明：两个会话对同一表的同一条记录进行修改。步骤1修改SCOTT工资为3500，没有提交或回滚之前，SCOTT记录处于加锁状态。步骤2的第二个会话对SCOTT进行修改处于等待状态。
步骤3解锁之后(即第一个会话对SCOTT的修改已经完成)，第二个会话挂起的修改此时可以执行。最后结果为第二个会话的修改结果，即SCOTT的工资修改为4000。读者可以使用查询语句检查。
以上是隐式加锁，用户也可以使用如下两种方式主动锁定行或表，防止其他会话对数据的修改。

SELECT FOR UPDATE:锁定表行，防止其他会话对行的修改
LOCK TABLE:锁定表，防止其他会话对表的修改

对emp表的部门10的雇员记录加显式锁，并测试。
步骤1：对部门10加显式锁：

Sql代码

1. SELECT empno,ename,job,sal FROM emp WHERE deptno=10 FOR UPDATE;  

SELECT empno,ename,job,sal FROM emp WHERE deptno=10 FOR UPDATE;

结果为：
    

Sql代码

1. EMPNO ENAME     JOB                 SAL   
2.         ------------------ -------------- ------------------ ------------------   
3.         7782  CLARK     MANAGER         2450   
4.         7839  KING          PRESIDENT           5000   
5.         7934  MILLER    CLERK               1300  

EMPNO ENAME     JOB                SAL

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

       7782  CLARK      MANAGER          2450

        7839  KING        PRESIDENT         5000

        7934  MILLER    CLERK             1300

步骤2：启动第二个SQL*Plus(第二个会话)，以SCOTT账户登录数据库，对部门10的雇员CLARK进行修改操作。

Sql代码

1. UPDATE emp SET sal=sal+100 where empno=7782;  

UPDATE emp SET sal=sal+100 where empno=7782;

执行结果：
没有任何输出(处于等待解锁状态)。
步骤3：在第一个会话进行解锁操作：

Sql代码

1. COMMIT;  

COMMIT;

步骤4：查看第二个会话，有输出结果：
已更新 1 行。
说明：步骤1对选定的部门10的雇员加锁，之后其他会话不能对部门10的雇员数据进行修改或删除。如果此时要进行修改或删除，则会处于等待状态。使用COMMIT语句进行解锁之后，如果有挂起的修改或删除操作，则等待的操作此时可以执行。
锁定表
LOCK语句用于对整张表进行锁定。语法如下：
LOCK TABLE 表名 IN {SHARE|EXCLUSIVE} MODE
对表的锁定可以是共享(SHARE)或独占(EXCLUSIVE)模式。共享模式下，其他会话可以加共享锁，但不能加独占锁。在独占模式下，其他会话不能加共享或独占锁。
【训练1】  对emp表添加独占锁。
步骤1：对emp表加独占锁：

Sql代码

1. LOCK TABLE emp IN EXCLUSIVE MODE;  

LOCK TABLE emp IN EXCLUSIVE MODE;

结果为：
表已锁定。

步骤2：对表进行解锁操作：

Sql代码

1. COMMIT;  

COMMIT;

说明：当使用LOCK语句显式锁定一张表时，死锁的概率就会增加。同样地，使用COMMIT或ROLLBACK命令可以释放锁。
   注意：必须没有其他会话对该表的任何记录加锁，此操作才能成功。