The lowest isolation level (1). The concurrency level is the highest. A dirty, non-repeatable or phantom read may occur for the tuple and a non-repeatable read may occur for the table as well. Similar to REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES(level 3) described above, but works differently for the table schema. That is, non-repeatable read due to 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 and update lock. However, the shared lock on the tuple is released immediately after it is retrieved. The intention lock on the table is released immediately after the retrieval as well.
session 1 |
session 2 |
---|---|
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 1;
Isolation level set to: READ COMMITTED SCHEMA, READ UNCOMMITTED INSTANCES. |
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 1;
Isolation level set to: READ COMMITTED SCHEMA, READ UNCOMMITTED INSTANCES. |
--creating a table
CREATE TABLE isol1_tbl(host_year integer, nation_code char(3)); CREATE UNIQUE INDEX on isol1_tbl(nation_code, host_year); INSERT INTO isol1_tbl VALUES (2008, 'AUS');
COMMIT; |
|
|
--selecting records from the table SELECT * FROM isol1_tbl; host_year nation_code =================================== 2008 'AUS' |
INSERT INTO isol1_tbl VALUES (2004, 'AUS');
INSERT INTO isol1_tbl VALUES (2000, 'NED');
/* able to insert new rows even if tran 2 uncommitted */ |
|
|
SELECT * FROM isol1_tbl; host_year nation_code =================================== 2008 'AUS' 2004 'AUS' 2000 'NED'
/* dirty read may occur so that tran_2 can select new rows uncommitted by tran_1 */ |
ROLLBACK; |
|
|
SELECT * FROM isol1_tbl; host_year nation_code =================================== 2008 'AUS'
/* unrepeatable read may occur so that selected results are different */ |
INSERT INTO isol1_tbl VALUES (1994, 'FRA');
DELETE FROM isol1_tbl WHERE nation_code = 'AUS' and host_year=2008;
/* able to delete rows while tran 2 is selecting rows*/ |
|
|
SELECT * FROM isol1_tbl; host_year nation_code =================================== 1994 'FRA' |
ALTER TABLE isol1_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 isol1_tbl; |
COMMIT; |
host_year nation_code gold ==================================== 1994 'FRA' NULL |