A relatively low isolation level (4). A dirty read does not occur, but non-repeatable or phantom read may. That is, transaction T1 can read another value because insert or update by transaction T2 is allowed while transaction T1 is repeatedly retrieving one object.
The following are the rules of this isolation level:
This isolation level uses a two-phase locking protocol for an exclusive lock. A shared lock on a row is released immediately after it is read; however, an intention lock on a table is released when a transaction terminates to ensure repeatable read on the schema.
The following example shows that a phantom or non-repeatable read may occur because another transaction can add or update a record while one transaction is performing the object read but repeatable read for the table schema update is ensured when the transaction level of the concurrent transactions is REPEATABLE READ CLASS with READ COMMITTED INSTANCES.
session 1 |
session 2 |
---|---|
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 4;
Isolation level set to: REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES. |
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 4;
Isolation level set to: REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES. |
--creating a table
CREATE TABLE isol4_tbl(host_year integer, nation_code char(3)); INSERT INTO isol4_tbl VALUES (2008, 'AUS');
COMMIT; |
|
--selecting records from the table SELECT * FROM isol4_tbl; host_year nation_code =================================== 2008 'AUS' |
|
INSERT INTO isol4_tbl VALUES (2004, 'AUS');
INSERT INTO isol4_tbl VALUES (2000, 'NED');
/* able to insert new rows even if tran 2 uncommitted */ |
|
SELECT * FROM isol4_tbl;
/* phantom read may occur when tran 1 committed */ |
|
COMMIT; |
host_year nation_code =================================== 2008 'AUS' 2004 'AUS' 2000 'NED' |
INSERT INTO isol4_tbl VALUES (1994, 'FRA'); |
|
SELECT * FROM isol4_tbl;
/* unrepeatable read may occur when tran 1 committed */ |
|
DELETE FROM isol4_tbl WHERE nation_code = 'AUS' and host_year=2008;
/* able to delete rows while tran 2 is selecting rows*/ |
|
COMMIT; |
host_year nation_code =================================== 2004 'AUS' 2000 'NED' 1994 'FRA' |
ALTER TABLE isol4_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 isol4_tbl; host_year nation_code =================================== 2004 'AUS' 2000 'NED' 1994 'FRA' |
|
|
COMMIT; |
SELECT * FROM isol4_tbl;
/* unable to access the table until tran_1 committed */ |
|
COMMIT; |
host_year nation_code gold =================================== 2004 'AUS' NULL 2000 'NED' NULL 1994 'FRA' NULL |