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 UNCOMMITTED INSTANCES

The lowest isolation level (1). The concurrency level is the highest. A dirty, non-repeatable or phantom read may occur for the rows 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:

  • Transaction T1 can read the record being updated by another transaction T2.
  • Transaction T1 can update/insert 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 and update lock. However, the shared lock on the rows is released immediately after it is retrieved. The intention lock on the table is released immediately after the retrieval as well.

Example

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