Oracle系列之七 并发与多版本控制

一、概述

并发是指多个会话同时访问同一资源。

多版本是指Oracle数据库可以同时物化多个版本的数据，是通过undo段来做到的。

二、事务隔离级别

Oracle数据库仅有三种事务隔离级别：Read Committed、Serializable和ReadOnly，其中Read Only是Oracle数据库特有的事务隔离级别。

通过三种现象来定义事务隔离级别的：

脏读：一个会话可以读到别的会话未提交的数据。

不可重复读：一个会话在不同时刻执行同一条sql可能会得到不同的结果。如连续运行同一条sql语句两次，查出的结果集是不同的

幻想读：一个会话T1时刻执行一条sql语句，然后到T2时刻又执行同一条sql语句，T1时刻看到的结果在T2时刻没有被修改，但是可能会多出数据。

四种事务隔离级别：

1、Read Uncommitted：会出现脏读、不可重复读和幻想读。

2、Read Committed：只会读到别人提交的数据，但是会出现不可重复读和幻想读。另外此处仅仅根据三种现象来判断，会存在这样一种情况：表T有100w行数据，会话S1对T的数量列进行累加，读取完第一条数据后，会话S2把第一行的数量减少了10，然后加到了最后一行并提交了事务，此时S1仍然没有读到最后一样。等S1读完最后一行并完成计算的时候，奇怪的事情发生了，数量竟然比正确的少了10。当然实际的数据库实现中，会通过比如加共享行锁或者多版本一致性读来避免这种情况发生。

Oracle数据库通过多版本来避免上边所说的问题的，Oracle读数据的时候不会加锁，除了显示的select ... for update。此级别下的读也叫语句级读一致性，在sql语句开始读那一刻起，所能读到的数据就已经确定了，就算在读取开始后，读到某一行前已经被别的会话删除了。

3、Repeatable Read：可以重复读，但是无法避免幻想读

 

4、Serializable：事务级别的隔离，在启动事务那一刻，在该事务中可以读到的数据就已经确定确定了，不会因为别的事务的修改而导致读到数据的在此事务内不一致，但是表结构的修改会受到影响。

4.1、事务级数据一致性

会话一：

SQL> set transaction isolation level serializable;

事务处理集。

SQL> select count(*) from tmp_emp;

  COUNT(*)
----------
        14

会话二：没有设置事务隔离级别，默认Read Committed

SQL> delete from tmp_emp;

已删除14行。

SQL> commit;

提交完成。

  会话一：重新查询表数据行数，依然是14

SQL> select count(*) from tmp_emp;

  COUNT(*)
----------
        14

  4.2、修改表结构

如果是在会话一种直接修改了表结构，比如删除了表tmp_emp，则serializable事务中也会受到影响。继续上边的例子：

会话一：

SQL> drop table tmp_emp;

表已删除。

  会话二：

SQL> select count(*) from tmp_emp;
select count(*) from tmp_emp
                     *
第 1 行出现错误:
ORA-00942: 表或视图不存在

如果此时我又在会话一中重新创建了同名同结构的表tmp_emp：

会话一：

SQL> create table tmp_emp as select * from emp;

表已创建。

  会话二：

SQL> select count(*) from tmp_emp;
select count(*) from tmp_emp
                     *
第 1 行出现错误:
ORA-08176: 一致读取失败; 回退数据不可用

  4.3、语句级隔离

而语句级别的这种隔离在serializable和read committed level是相同的，清空两个会话事务隔离级别，重新开始：

会话一：

SQL> commit;
SQL> set transaction isolation level serializable;

事务处理集。
SQL> select count(*) from tmp_emp;

  COUNT(*)
----------
        14
SQL> variable x refcursor;
SQL> begin
  2  open :x for select * from emp;
  3  end;
  4  /

PL/SQL 过程已成功完成。

  会话二：

SQL> select count(*) from tmp_emp;

  COUNT(*)
----------
        14

SQL> drop table tmp_emp;

表已删除。

  会话一：

SQL> print x;

     EMPNO ENAME      JOB              MGR HIREDATE              
---------- ---------- --------- ---------- -------------- ---------- 
      7499 ALLEN      SALESMAN        7698 20-2月 -81     
......
已选择14行。

  4.4、同时修改一条数据记录

如果两个serializable level的事务试图去修改同一条记录，则后边修改的会话会被阻塞，前边修改的会话提交事务后，后边修改的会话会报错：

会话一：

SQL>set transaction isolation level serializable;
事务处理集。

SQL> update tmp_emp set sal = sal + 1 where empno = 7934;

已更新 1 行。

会话二：

SQL> set transaction isolation level serializable ;

事务处理集。

SQL> update tmp_emp set sal = sal + 100 where empno = 7934;

  会话一提交事务：

SQL> commit;

提交完成。

  会话二出错：

update tmp_emp set sal = sal + 100 where empno = 7934
       *
第 1 行出现错误:
ORA-08177: 无法连续访问此事务处理

从上也看的出Serializable并不是表示事务表现的跟串行的一样一个一个的执行。

4.6、此处序列化并不意味这用户事务是串行执行的：

会话一：

SQL> create table t2 (x int);

表已创建。

 

SQL> set transaction isolation level serializable;

事务处理集。

  会话二：

SQL> set transaction isolation level serializable;

事务处理集。

SQL> insert into t2 select count(*) from t2;

已创建 1 行。

  会话一：

SQL> insert into t2 select count(*) from t2;

已创建 1 行。

SQL> commit;

提交完成。

  会话二：

SQL> commit;

提交完成。

SQL> select * from t2;

         X
----------
         0
         0

  4.7、此事务隔离界别可能存在的问题：

如果undo段过小，在serializable事务中，查询时候可能会出现如下错误：

ERROR:
ORA-01555: 快照过旧: 回退段号 14 (名称为 "_SYSSMU14_3676531858$") 过小

  可以通过这种方式创建小的回滚表空间：

SQL> create undo tablespace undo_small
  2  datafile 'D:\oracle\oradata\orc11gr2\undo_2m.dbf' size 2m
  3  autoextend off
  4  /
表空间已创建。

SQL> alter system set undo_tablespace=undo_small;
系统已更改。

  然后一个事务开启Serializable级别，另外一个一直在插入数据，然后不断删除数据就会出现问题了。

4.8、此事务隔离级别适用于以下情况：要获得事务级一致性；事务比较短；并发比较低。因为并发高的时候，Serializable事务中修改了别人修改过的数据会出错。

5、Read Only：Oracle数据库特有的隔离级别，在这种隔离级别下，只能读取数据，不能修改数据。此级别具备Serializable级别的特征。

严格的说，此级别根本就不是隔离级别，就连设置事务级别的语法都不同：

SQL> set transaction read only;

事务处理集。

  此事务中的数据不能被修改：

SQL> update emp set sal = sal + 1;
update emp set sal = sal + 1
       *
第 1 行出现错误:
ORA-01456: 不能在 READ ONLY 事务处理中执行插入/删除/更新操作

  别的会话修改的数据，在此read only事务不提交的情况下是看不到的。虽然此事务是只读，不会修改数据，但是仍然可以commit，commit后启动新的事务。

此级别支持报告需求，即相对于某个时间点，报告的内容应该是一致的。

此级别也可能出现ORA-01555错误。

三、多版本控制读一执性

1、数据仓库中可能出现的错误

考虑这样一种情形：

1.1、每个小时整点时候，从生产库拉表T数据到数据仓库，T上边有一列时间列lastUpdateTime，每次更新时候会记录下当时时间。整点时候就根据lastUpdateTime>time1 and lastUpdateTime <= time1 + 1

1.2、现在出现这么一种情形，某事务11:59:58开始，并把T的lastUpdateTime修改为了11:59:58，在12:00:50完成。拉数据的动作从12:00开始，12:00:30完成。由于事物隔离性和写不阻塞读，12:00拉数据时候，看不到11:59:58时候的数据，没有拉进数据仓库。

1.3、13:00时候，拉lastUpdateTime > 12:00 and lastUpdateTime < 13:00的数据，仍然没有拉出上边那条数据。此时数据已经出错了。

如何解决这种问题呢？通过如下sql获取开始时间：

SQL>  select nvl(min(start_time),sysdate) starttime from v$transaction;

STARTTIME
----------------------------------------
11/15/11 00:25:35

  2、热块上超出期望的IO

会话一：

SQL> select * from scott.t2 where x = 3;
统计信息
----------------------------------------------------------
          1  recursive calls
          1  db block gets
       948 consistent gets
          0  physical reads

  会话二：

SQL> insert into t2 select x + 160000 from t2;

已创建303104行。

  会话一：

SQL> select * from scott.t2 where x = 3;
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       3056  consistent gets
          0  physical reads

  此时会话一的一致性读多出了2000多，因为会话二修改了数据块，但是没提交事务，会话一必须把读到的数据块中的数据前滚到会话二修改前的状态。

SQL> select file#,block#,count(*)
  2  from v$bh
  3  group by file#,block#
  4  having count(*) > 3
  5  order by 3 desc;

     FILE#     BLOCK#   COUNT(*)
---------- ---------- ----------
         1      86174          6
         1      77979          6
         4      28313          6
         1      10113          6
         4      28345          6

从这个查询看，数据库在一时间点上对一数据块缓存的版本数一般不超过6个，但是这些版本可以由需要它们的任何查询使用。

 

在生产环境中，高并发情况下可能会出现大量的这种问题，一些数据的读取很耗时。

四、写一致性

考虑这样一种问题，上边说到查询的时候（还是读取数据的时候？）会利用多版本达到读一致性，在修改数据的时候是先读取到相关数据，如果还读一致性岂不是有问题，我们更新的可能不是最新的数据？比如，我们启动更新的时候，更新了在更新到最后一行之前，另外一个事务更新了最后一行并提交了事务，等我们更新到最后一样的时候i，会根据读一致性，读取启动读时候的数据版本，但是此时数据库的数据已经被更新，我们再去更新最新版本的前映像，是会导致数据错误的。

如何解决这种问题呢？Oracle数据库引入了写一致性！

读一致性：读取到启动读时候相应版本的数据。

写一致性：真正要写的时候读取到当前最新版本的数据。

那写的时候，到底是什么样子呢？来看个例子

会话一：

SQL> create table t (x int,y int);
表已创建。

SQL> insert into t values(1,1);
已创建 1 行。

SQL> insert into t values(2,2);
已创建 1 行。

SQL> create or replace trigger restart_t
  2  before update on t for each row
  3  begin
  4     dbms_output.put_line('old.x: ' || :old.x || 'new.x: ' || :new.x);
  5  end;
  6  /
触发器已创建

SQL> update t set x = x+1 where y = 1;
old.x: 1new.x: 2
已更新 1 行。

  会话二：

SQL> update t set x = x - 1 where y = 1;

  此时会一直等待，等到会话一提交事务后。

会话一：

SQL> commit;
提交完成。

  会话二：

old.x: 1new.x: 0
old.x: 2new.x: 1

已更新 1 行。

此处触发器执行了两次，为何？？因为oracle的重启动！ 

如果数据库有1w条记录，最后一条被锁住了，是不是要重启动整个更新过程？

会话一：

SQL> update t set x = rownum,y = rownum;
已更新18行。

SQL> commit;
提交完成。

SQL> update t set x = x + 1 where y = 18;
old.x: 18new.x: 19
已更新 1 行。

  会话二（阻塞）：

SQL> update t set x = x + 1 ;

  会话一：

SQL> commit;

提交完成。

  会话二：

old.x: 1new.x: 2
......
old.x: 18new.x: 19
old.x: 1new.x: 2
......
old.x: 19new.x: 20

已更新18行。

整个过程都重启动了！ 

再来看下另外一种情况：

会话一；

SQL> update t set y = y + 1 where x = 17;
old.x: 17new.x: 17
已更新 1 行。

  会话二：

SQL> update t set y = y + 1 ;

  会话一提交后，会话二：

old.x: 1new.x: 1
......
old.x: 19new.x: 19

已更新18行。

  这次，没有重启动！也就是说重启动是由于读被更新了的数据导致的？

重启动的原则是：会拿where字句中查找行所用的列的一致读的列的版本和触发器中的所用列的当前读的版本比较，如果相同就不会重启动，不同就会重启动。

为什么要注意重启动呢？

1、如果触发器中修改了别的数据，重启动可能会导致重复修改；

2、如果触发器中做了一些别的什么事情，如发邮件，重启动可能会导致重复发送；

3、或者在触发器重修改了文件，重启动可能导致重复修改。

我们可以在逻辑允许的情况下，通过after update on table for each row来避免重启动。

ps：删除触发器的语句：

SQL> drop trigger restart_t;

触发器已删除。