비교적 높은 격리 수준(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. |