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

A relatively low isolation level (4). A dirty read does not occur, but non-repeatable or phantom read may. That is, transaction T1 can read another value because insert or update by transaction T2 is allowed while transaction T1 is repeatedly retrieving one object.

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 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 lock. A shared lock on a row is released immediately after it is read; however, an intention lock on a table is released when a transaction terminates to ensure repeatable read on the schema.

Example

The following is an example that shows that a phantom or non-repeatable read may occur because another transaction can add or update a record while one transaction is performing the object read but repeatable read for the table schema update is ensured when the transaction level of the concurrent transactions is REPEATABLE READ CLASS with READ COMMITTED INSTANCES.

session 1

session 2

;autocommit off

AUTOCOMMIT IS OFF

 

SET TRANSACTION ISOLATION LEVEL 4

;xr

 

Isolation level set to:

REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES.

;autocommit off

AUTOCOMMIT IS OFF

 

SET TRANSACTION ISOLATION LEVEL 4

;xr

 

Isolation level set to:

REPEATABLE READ SCHEMA, READ COMMITTED INSTANCES.

--creating a table

 

CREATE TABLE isol4_tbl(host_year integer, nation_code char(3));

INSERT INTO isol4_tbl VALUES (2008, 'AUS');

 

COMMIT;

;xr 

 
 

--selecting records from the table

SELECT * FROM isol4_tbl;

;xr

 

=== <Result of SELECT Command> ===

    host_year  nation_code

===================================

         2008  'AUS'

 

1 rows selected.

INSERT INTO isol4_tbl VALUES (2004, 'AUS');

 

INSERT INTO isol4_tbl VALUES (2000, 'NED');

;xr

 

2 rows affected.

 

/* able to insert new rows even if tran 2 uncommitted */

 
 

SELECT * FROM isol4_tbl;

;xr

 

/* phantom read may occur when tran 1 committed */

COMMIT;

;xr

=== <Result of SELECT Command> ===

    host_year  nation_code

===================================

         2008  'AUS'

         2004  'AUS'

         2000  'NED'

 

3 rows selected.

INSERT INTO isol4_tbl VALUES (1994, 'FRA');

;xr

 

1 rows affected.

 
 

SELECT * FROM isol4_tbl;

;xr

 

/* unrepeatable read may occur when tran 1 committed */

DELETE FROM isol4_tbl

WHERE nation_code = 'AUS' and

host_year=2008;

;xr

 

1 rows affected.

 

/* able to delete rows while tran 2 is selecting rows*/

 

COMMIT;

;xr

=== <Result of SELECT Command> ===

    host_year  nation_code

===================================

         2004  'AUS'

         2000  'NED'

         1994  'FRA'

 

3 rows selected.

ALTER TABLE isol4_tbl

ADD COLUMN gold INT;

;xr

 

/* unable to alter the table schema until tran 2 committed */

 
 

/* repeatable read is ensured while tran_1 is altering table schema */

 

SELECT * FROM isol4_tbl;

;xr

 

=== <Result of SELECT Command> ===

    host_year  nation_code

===================================

         2004  'AUS'

         2000  'NED'

         1994  'FRA'

 

3 rows selected.

1 command(s) successfully processed.

COMMIT;

;xr

 

SELECT * FROM isol4_tbl;

;xr

 

/* unable to access the table until tran_1 committed */

COMMIT;

;xr

=== <Result of SELECT Command > ===

host_year  nation_code  gold

===================================

  2004  'AUS'           NULL

  2000  'NED'           NULL

  1994  'FRA'           NULL

 

3 rows selected.