Oracle默认事务隔离级别

验证Oracle默认事务隔离级别

首先session 1中两次获取同一条数据：

-- 关于oracle事务隔离级别 session 1
declare 
  v_old VARCHAR2(32);
  v_now VARCHAR2(32);
  
  v_sysdate DATE;
  v_limit DATE ;
  pragma autonomous_transaction;
BEGIN
  
  SELECT pm.old_polno INTO v_old from pol_main pm WHERE pm.polno = 'GP25000000000003';
  dbms_output.put_line('before sleep: ' || v_old || ' sysdate:' || 
                       to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') ); 
                       
  SELECT SYSDATE INTO v_sysdate from dual;            
  SELECT SYSDATE + 1/1440 /12 INTO v_limit from dual;             
  LOOP
    IF SYSDATE > v_limit THEN 
      EXIT;
    END IF;   
  END LOOP;
  
  SELECT pm.old_polno INTO v_now from pol_main pm WHERE pm.polno = 'GP25000000000003';
  dbms_output.put_line('fater sleep: ' || v_now || ' sysdate:' ||  
                       to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));                        
  
  COMMIT;
end;

在LOOP的几秒内执行：

UPDATE pol_main pm SET pm.old_polno = 'haha' where pm.polno ='GP25000000000003';
COMMIT;

最后输出：

before sleep:  sysdate:2014-04-03 10:38:18
fater sleep: haha sysdate:2014-04-03 10:38:24

可见，Oracle隔离级别是：

能读取已提交的事务，但是存在不可重复读的问题。默认隔离级别应该是：read committed.

Oracle数据库支持READ COMMITTED 和 SERIALIZABLE这两种事务隔离级别。
所以Oracle不支持脏读.

SQL标准所定义的默认事务隔离级别是SERIALIZABLE，但是Oracle 默认使用的是READ COMMITTED

设置隔离级别使用

SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

测试Oracle的串行:（注意第一行设置了隔离级别，且后续取消了自治事务，是匿名块在同一个事务中）

-- 关于oracle事务隔离级别 session 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

declare 
  v_old VARCHAR2(32);
  v_now VARCHAR2(32);
  
  v_sysdate DATE;
  v_limit DATE ;
  --pragma autonomous_transaction;
BEGIN
  --SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  SELECT pm.old_polno INTO v_old from pol_main pm WHERE pm.polno = 'GP25000000000003';
  dbms_output.put_line('before sleep: ' || v_old || ' sysdate:' || 
                       to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') ); 
                       
  SELECT SYSDATE INTO v_sysdate from dual;            
  SELECT SYSDATE + 1/1440 /12 INTO v_limit from dual;             
  LOOP
    IF SYSDATE > v_limit THEN 
      EXIT;
    END IF;   
  END LOOP;
  
  SELECT pm.old_polno INTO v_now from pol_main pm WHERE pm.polno = 'GP25000000000003';
  dbms_output.put_line('fater sleep: ' || v_now || ' sysdate:' ||  
                       to_char(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));                        
  
  COMMIT;
end;
/

其中执行：

UPDATE pol_main pm SET pm.old_polno = 'after' where pm.polno ='GP25000000000003';
COMMIT;

输出

before sleep: before sysdate:2014-04-03 11:26:23
fater sleep: before sysdate:2014-04-03 11:26:29

PS. 延迟可以用dbms_lock.sleep(5)，这里没有权限运行.