Oracle下演示Nonrepeatable Read 和Phantom Read

Nonrepeatable Read 不可重复读和Phantom Read幻读概念：

Non-repeatable Read不可重复读：在一个事务中，同样的数据被2次读取，得到不同的结果集

Phantom Read幻读：在一个事务中，同样的sql被2次执行，得到不同的结果集。

不可重复读的重点是修改:同样的条件, 你读取过的数据, 再次读取出来发现值不一样了
幻读的重点在于：新增或者删除同样的条件, 第1次和第2次读出来的记录数不一样
从锁的角度来看, 两者的区别就比较大：
对于前者, 只需要锁住满足条件的记录
对于后者, 要锁住满足条件及其相近的记录

首先需要给用户sys.dbms_lock的执行权限，不然会报错。
SQL> conn / as sysdba
Connected.
SQL> grant execute on sys.dbms_lock to bys;
Grant succeeded.
报的错是：
SQL> show error
Errors for PROCEDURE NO_REPEATABLE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3      PL/SQL: Statement ignored
7/3      PLS-00201: identifier 'DBMS_LOCK' must be declared

实验如下：

1.Non-repeatable Read不可重复读

会话1：
SQL> set serveroutput on
SQL> set time on
11:48:16 SQL>
11:49:24 SQL> create or replace procedure aa(interval int)
           2  as
           3  x varchar2(10);
           4  y varchar2(10);
           5  begin
           6    select a into x from test where rownum=1;
           7    dbms_lock.sleep(interval);
           8    select a into y from test where rownum=1;
           9    dbms_output.put_line('first--'||x);
          10    dbms_output.put_line('second--'||y);
          11    end aa;
          12  /

Procedure created
11:49:25 SQL> exec aa(20);
first--3
second--999
PL/SQL procedure successfully completed

会话2：
11:48:23 SQL> select * from test
         A
----------
         3
11:48:31 SQL>
11:49:39 SQL> update test set a=999 where a=3;
1 row updated
11:49:42 SQL> commit;
Commit complete

2.Phantom Read幻读：在一个事务中，同样的sql被2次执行，得到不同的结果集。

会话1：
11:18:31 SQL> select count(*) from test;
  COUNT(*)
----------
     23798
create or replace procedure phantom(interval int)
as
x varchar2(10);
y varchar2(10);
begin
select count(*) into x from test;
dbms_lock.sleep(interval);
select count(*) into y from test;
dbms_output.put_line('first--'||x);
dbms_output.put_line('second--'||y);
end phantom;
  /

Procedure created.
11:21:12 SQL> set serveroutput on;
11:21:55 SQL>
11:22:15 SQL> exec phantom(20);             ---执行此存储过程，20是赋值给dbms_lock.sleep(interval); 休眠20秒
first--20799
second--17800

PL/SQL procedure successfully completed.
11:22:38 SQL>

会话2：在会话1执行存储过程时删除一部分数据
11:22:10 SQL> select count(*) from test;
  COUNT(*)
----------
     20799
11:22:12 SQL> delete test where rownum<3000;
2999 rows deleted.
11:22:22 SQL> commit;
Commit complete.
11:22:25 SQL> select count(*) from test;
  COUNT(*)
----------
     17800