REPEATABLE READ CLASS with REPEATABLE READ INSTANCES

비교적 높은 격리 수준(5)으로서, 더티 읽기, 반복 불가능한 읽기가 발생하지 않지만, 유령 읽기는 발생할 수 있다.

다음과 같은 규칙이 적용된다.

이 격리 수준은 2단계 잠금 프로토콜을 따른다.

예제

다음은 동시에 수행되는 트랜잭션의 격리 수준이 REPEATABLE READ CLASS with REPEATABLE READ INSTANCES인 경우 한 트랜잭션에서 객체 읽기를 수행하는 동안 다른 트랜잭션이 새로운 레코드를 추가할 수 있으므로 유령 읽기가 발생할 수 있음을 보여주는 예제이다.

session 1

session 2

;autocommit off

AUTOCOMMIT IS OFF

 

SET TRANSACTION ISOLATION LEVEL 5

;xr

 

Isolation level set to:

REPEATABLE READ SCHEMA, REPEATABLE READ INSTANCES.

;autocommit off

AUTOCOMMIT IS OFF

 

SET TRANSACTION ISOLATION LEVEL 5

;xr

 

Isolation level set to:

REPEATABLE READ SCHEMA, REPEATABLE READ INSTANCES.

--creating a table

 

CREATE TABLE isol5_tbl(host_year integer, nation_code char(3));

CREATE UNIQUE INDEX on isol5_tbl(nation_code, host_year);

INSERT INTO isol5_tbl VALUES (2008, 'AUS');

INSERT INTO isol5_tbl VALUES (2004, 'AUS');

 

COMMIT;

;xr

 
 

--selecting records from the table

SELECT * FROM isol5_tbl WHERE nation_code='AUS';

;xr

 

=== <Result of SELECT Command> ===

    host_year  nation_code

===================================

         2004  'AUS'

         2008  'AUS'

 

2 rows selected.

INSERT INTO isol5_tbl VALUES (2004, 'KOR');

INSERT INTO isol5_tbl VALUES (2000, 'AUS');

;xr

 

2 rows affected.

 

/* able to insert new rows only when locks are not conflicted */

 
 

SELECT * FROM isol5_tbl WHERE nation_code='AUS';

;xr

 

/* phantom read may occur when tran 1 committed */

COMMIT;

;xr

=== <Result of SELECT Command> ===

    host_year  nation_code

===================================

         2000  'AUS'

         2004  'AUS'

         2008  'AUS'

 

3 rows selected.

DELETE FROM isol5_tbl

WHERE nation_code = 'AUS' and

host_year=2008;

;xrun

 

/* unable to delete rows until tran 2 committed */

 

1 rows affected.

 

1 command(s) successfully processed.

COMMIT;

;xr

 

SELECT * FROM isol5_tbl WHERE nation_code = 'AUS';

;xr

 

/* unable to select rows until tran 1 committed */

COMMIT;

;xr

=== <Result of SELECT Command> ===

    host_year  nation_code

===================================

         2000  'AUS'

         2004  'AUS'

 

2 rows selected.

ALTER TABLE isol5_tbl

ADD COLUMN gold INT;

;xr

 

/* unable to alter the table schema until tran 2 committed */

 
 

/* repeatable read is ensured while tran_1 is altering table schema */

 

SELECT * FROM isol5_tbl WHERE nation_code = 'AUS';

;xr

 

=== <Result of SELECT Command> ===

    host_year  nation_code

===================================

         2000  'AUS'

         2004  'AUS'

 

2 rows selected.

1 command(s) successfully processed.

COMMIT;

;xr

 

SELECT * FROM isol5_tbl WHERE nation_code = 'AUS';

;xr

/* unable to access the table until tran_1 committed */

COMMIT;

;xr

=== <Result of SELECT Command > ===

host_year  nation_code  gold

===================================

  2000  'AUS'           NULL

  2004  'AUS'           NULL

 

2 rows selected.