A relatively high isolation level (5). A dirty or non-repeatable read does not occur, but a phantom read may.
The following are the rules of this isolation level:
This isolation level uses a two-phase locking protocol.
The following example shows that phantom read may occur because another transaction can add a new record while one transaction is performing the object read when the transaction level of the concurrent transactions is REPEATABLE READ CLASS with REPEATABLE READ INSTANCES.
session 1 |
session 2 |
---|---|
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 5;
Isolation level set to: REPEATABLE READ SCHEMA, REPEATABLE READ INSTANCES. |
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 5;
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; |
|
--selecting records from the table SELECT * FROM isol5_tbl WHERE nation_code='AUS'; host_year nation_code =================================== 2004 'AUS' 2008 'AUS' |
|
INSERT INTO isol5_tbl VALUES (2004, 'KOR'); INSERT INTO isol5_tbl VALUES (2000, 'AUS');
/* able to insert new rows only when locks are not conflicted */ |
|
SELECT * FROM isol5_tbl WHERE nation_code='AUS';
/* phantom read may occur when tran 1 committed */ |
|
COMMIT; |
host_year nation_code =================================== 2000 'AUS' 2004 'AUS' 2008 'AUS' |
DELETE FROM isol5_tbl WHERE nation_code = 'AUS' and host_year=2008;
/* unable to delete rows until tran 2 committed */ |
|
|
COMMIT; |
SELECT * FROM isol5_tbl WHERE nation_code = 'AUS';
/* unable to select rows until tran 1 committed */ |
|
COMMIT; |
host_year nation_code =================================== 2000 'AUS' 2004 'AUS' |
ALTER TABLE isol5_tbl ADD COLUMN gold INT;
/* 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'; host_year nation_code =================================== 2000 'AUS' 2004 'AUS' |
|
|
COMMIT; |
SELECT * FROM isol5_tbl WHERE nation_code = 'AUS'; /* unable to access the table until tran_1 committed */ |
|
COMMIT; |
host_year nation_code gold =================================== 2000 'AUS' NULL 2004 'AUS' NULL |