A relatively low isolation level (2). A dirty read does not occur, but non-repeatable or phantom read may occur. That is, this level is similar to REPEATABLE READ CLASS with READ COMMITTED INSTANCES(level 4) described above, but works differently for table schema. Non-repeatable read due to a table schema update may occur because another transaction T2 can change the schema of the table being viewed by the transaction T1.
The following are the rules of this isolation level:
This isolation level uses a two-phase locking protocol for an exclusive lock. However, non-repeatable read may occur because the shared lock on the tuple is released immediately after it is retrieved and the intention lock on the table is released immediately as well.
The following example shows that phantom or non-repeatable read for the record as well as for the table schema may occur because another transaction can add or update a new record while one transaction is performing the object read when the transaction level of the concurrent transactions is READ COMMITTED CLASS with READ COMMITTED INSTANCES.
session 1 |
session 2 |
---|---|
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 2;
Isolation level set to: READ COMMITTED SCHEMA, READ COMMITTED INSTANCES. |
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 2;
Isolation level set to: READ COMMITTED SCHEMA, READ COMMITTED INSTANCES. |
--creating a table
CREATE TABLE isol2_tbl(host_year integer, nation_code char(3)); CREATE UNIQUE INDEX on isol2_tbl(nation_code, host_year); INSERT INTO isol2_tbl VALUES (2008, 'AUS');
COMMIT; |
|
|
--selecting records from the table SELECT * FROM isol2_tbl; host_year nation_code =================================== 2008 'AUS' |
INSERT INTO isol2_tbl VALUES (2004, 'AUS');
INSERT INTO isol2_tbl VALUES (2000, 'NED');
/* able to insert new rows even if tran 2 uncommitted */ |
|
|
SELECT * FROM isol2_tbl;
/* phantom read may occur when tran 1 committed */ |
COMMIT; |
host_year nation_code =================================== 2008 'AUS' 2004 'AUS' 2000 'NED' |
INSERT INTO isol2_tbl VALUES (1994, 'FRA'); |
|
|
SELECT * FROM isol2_tbl;
/* unrepeatable read may occur when tran 1 committed */ |
DELETE FROM isol2_tbl WHERE nation_code = 'AUS' and host_year=2008;
/* able to delete rows even if tran 2 uncommitted */ |
|
COMMIT; |
host_year nation_code =================================== 2004 'AUS' 2000 'NED' 1994 'FRA' |
ALTER TABLE isol2_tbl ADD COLUMN gold INT;
/* able to alter the table schema even if tran 2 is uncommitted yet*/ |
|
|
/* unrepeatable read may occur so that result shows different schema */
SELECT * FROM isol2_tbl; |
COMMIT; |
host_year nation_code gold =================================== 2004 'AUS' NULL 2000 'NED' NULL 1994 'FRA' NULL |