Demonstrate NonrepeatableRead and PhantomRead in Oracle
Demonstrate Nonrepeatable Read and Phantom Read in Oracle
Nonrepeatable Read:
Non-repeatable Read: in a transaction, the same data is Read twice to obtain different result sets.
Phantom Read: in a transaction, the same SQL statement is executed twice to obtain different result sets.
The key to unrepeatable reading is to modify: The same condition indicates that the data you read is read again and the values are different.
The focus of phantom read is to add or delete the same conditions. The number of records read for 1st and 2nd is different.
From the lock point of view, the difference between the two is relatively large:
For the former, you only need to lock records that meet the conditions.
For the latter, it is necessary to lock records that meet the conditions and their similarity
First, you must grant the execution permission of sys. dbms_lock to the user. Otherwise, an error will be reported.
SQL> conn/as sysdba
Connected.
SQL> grant execute on sys. dbms_lock to bys;
Grant succeeded.
The error message is:
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
The experiment is as follows: 1. Non-repeatable Read does not allow repeated Read sessions. 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
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
Session 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: in a transaction, the same SQL statement is executed twice to obtain different result sets. Session 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); --- execute this stored procedure, 20 is assigned to dbms_lock.sleep (interval); sleep for 20 seconds
First -- 20799
Second -- 17800
PL/SQL procedure successfully completed.
11:22:38 SQL>
Session 2: delete part of data when Session 1 executes the Stored Procedure
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
