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 | 



REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES

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:

  • 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 cannot 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 tuple is released immediately after it is retrieved. The intention lock on the table is released when the transaction ends to ensure repeatable reads.

Example

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.