首先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),这里没有权限运行、、、