The default isolation of CUBRID (3). The concurrency level is high. A dirty, non-repeatable or phantom read may occur for the tuple, but repeatable read is ensured for the table. That is, transaction T2 can read an object while transaction T1 is updating one.
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 when the transaction ends to ensure repeatable reads.
The following example shows that another transaction can read dirty data uncommitted by one transaction but repeatable reads are ensured for table schema update when the transaction level of the concurrent transactions is REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES.
session 1 |
session 2 |
---|---|
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 3;
Isolation level set to: REPEATABLE READ SCHEMA, READ UNCOMMITTED INSTANCES. |
;autocommit off AUTOCOMMIT IS OFF
SET TRANSACTION ISOLATION LEVEL 3;
Isolation level set to: REPEATABLE READ SCHEMA, READ UNCOMMITTED INSTANCES. |
--creating a table
CREATE TABLE isol3_tbl(host_year integer, nation_code char(3)); CREATE UNIQUE INDEX on isol3_tbl(nation_code, host_year); INSERT INTO isol3_tbl VALUES (2008, 'AUS');
COMMIT; |
|
|
--selecting records from the table SELECT * FROM isol3_tbl; host_year nation_code =================================== 2008 'AUS' |
INSERT INTO isol3_tbl VALUES (2004, 'AUS');
INSERT INTO isol3_tbl VALUES (2000, 'NED');
/* able to insert new rows even if tran 2 uncommitted */ |
|
|
SELECT * FROM isol3_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 isol3_tbl; host_year nation_code =================================== 2008 'AUS'
/* unrepeatable read may occur so that selected results are different */ |
INSERT INTO isol3_tbl VALUES (1994, 'FRA');
DELETE FROM isol3_tbl WHERE nation_code = 'AUS' and host_year=2008;
/* able to delete rows even if tran 2 uncommitted */ |
|
|
SELECT * FROM isol3_tbl; host_year nation_code =================================== 1994 'FRA' |
ALTER TABLE isol3_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 isol3_tbl; host_year nation_code =================================== 1994 'FRA' |
|
COMMIT; |
|
SELECT * FROM isol3_tbl; |
COMMIT; |
host_year nation_code gold =================================== 1994 'FRA' NULL |
Note CUBRID flushes dirty data (or dirty instances) in the client buffers to the database (server) such as the following situations. For details, see How to Handle Dirty Instances.