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 REPEATABLE READ INSTANCES

A relatively high isolation level (5). A dirty or non-repeatable read does not occur, but a phantom read may.

The following are the rules of this isolation level:

  • Transaction T1 cannot read or modify the record being updated by another transaction T2.
  • Transaction T1 cannot read or modify the record being viewed by another transaction T2.
  • Another transaction T2 can insert a new record into table A while transaction T1 is retrieving records of table A. However, transaction T1 and T2 cannot set the lock on the same record.

This isolation level uses a two-phase locking protocol.

Example

The following example shows that phantom read may occur because another transaction can add a new record while one transaction is performing the object read when the transaction level of the concurrent transactions is REPEATABLE READ CLASS with REPEATABLE READ INSTANCES.

session 1

session 2

;autocommit off

AUTOCOMMIT IS OFF

 

SET TRANSACTION ISOLATION LEVEL 5;

 

Isolation level set to:

REPEATABLE READ SCHEMA, REPEATABLE READ INSTANCES.

;autocommit off

AUTOCOMMIT IS OFF

 

SET TRANSACTION ISOLATION LEVEL 5;

 

Isolation level set to:

REPEATABLE READ SCHEMA, REPEATABLE READ INSTANCES.

--creating a table

 

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

CREATE UNIQUE INDEX on isol5_tbl(nation_code, host_year);

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

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

 

COMMIT;

 
 

--selecting records from the table

SELECT * FROM isol5_tbl WHERE nation_code='AUS';

    host_year  nation_code

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

         2004  'AUS'

         2008  'AUS'

INSERT INTO isol5_tbl VALUES (2004, 'KOR');

INSERT INTO isol5_tbl VALUES (2000, 'AUS');

 

/* able to insert new rows only when locks are not conflicted */

 
 

SELECT * FROM isol5_tbl WHERE nation_code='AUS';

 

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

COMMIT;

    host_year  nation_code

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

         2000  'AUS'

         2004  'AUS'

         2008  'AUS'

DELETE FROM isol5_tbl

WHERE nation_code = 'AUS' and

host_year=2008;

 

/* unable to delete rows until tran 2 committed */

 

 

COMMIT;

 

SELECT * FROM isol5_tbl WHERE nation_code = 'AUS';

 

/* unable to select rows until tran 1 committed */

COMMIT;

    host_year  nation_code

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

         2000  'AUS'

         2004  'AUS'

ALTER TABLE isol5_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 isol5_tbl WHERE nation_code = 'AUS';

    host_year  nation_code

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

         2000  'AUS'

         2004  'AUS'

 

COMMIT;

 

SELECT * FROM isol5_tbl WHERE nation_code = 'AUS';

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

COMMIT;

host_year  nation_code  gold

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

  2000  'AUS'           NULL

  2004  'AUS'           NULL