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 | 

SERIALIZABLE

The highest isolation level (6). Problems concerning concurrency (e.g. dirty read, non-repeatable read, phantom read, etc.) do not occur.

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 cannot insert a new record into table A while transaction T1 is retrieving the records of table A.

This isolation level uses a two-phase locking protocol for shared and exclusive lock: the lock is held until the transaction ends even after the operation has been executed.

Example

The following example shows that another transaction cannot access the table or record while one transaction is reading or updating the object when the transaction level of the concurrent transactions is SERIALIZABLE.

session 1

session 2

;autocommit off

AUTOCOMMIT IS OFF

 

SET TRANSACTION ISOLATION LEVEL 6;

 

Isolation level set to:

SERIALIZABLE

;autocommit off

AUTOCOMMIT IS OFF

 

SET TRANSACTION ISOLATION LEVEL 6;

 

Isolation level set to:

SERIALIZABLE

--creating a table

 

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

 

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

 

COMMIT;

 

 

--selecting records from the table

SELECT * FROM isol6_tbl WHERE nation_code = 'AUS';

    host_year  nation_code

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

         2008  'AUS'

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

/* unable to insert a row until the tran 2 committed */

 

 

COMMIT;

 

SELECT * FROM isol6_tbl WHERE nation_code = 'AUS';

 

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

COMMIT;

    host_year  nation_code

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

         2008  'AUS'

         2004  'AUS'

DELETE FROM isol6_tbl

WHERE nation_code = 'AUS' and

host_year=2008;

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

 

 

COMMIT;

 

SELECT * FROM isol6_tbl WHERE nation_code = 'AUS';

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

COMMIT;

    host_year  nation_code

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

         2004  'AUS'

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

    host_year  nation_code

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

         2004  'AUS'

 

COMMIT;

 

SELECT * FROM isol6_tbl WHERE nation_code = 'AUS';

 

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

COMMIT;

host_year  nation_code  gold

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

  2004  'AUS'           NULL