Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 



READ COMMITTED CLASS with READ COMMITTED INSTANCES

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:

  • Transaction T1 cannot read the record being updated by another transaction T2.
  • Transaction T1 can update/insert a record to the table being viewed by another transaction T2.
  • Transaction T1 can change the schema of the table being viewed by another transaction T2.

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.

Example

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