Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

SET TRANSACTION ISOLATION LEVEL

Description

You can set the level of transaction isolation by using isolation_level and the SET TRANSACTION statement in the $CUBRID/conf/cubrid.conf. The level of REPEATABLE READ CLASS and READ UNCOMMITTED INSTANCES are set by default, which indicates the level 3 through level 1 to 6. For more information, see Database Concurrency.

Syntax

SET TRANSACTION ISOLATION LEVEL isolation_level_spec [ ; ]

isolation_level_spec:

_ SERIALIZABLE

_ CURSOR STABILITY

_ isolation_level [ { CLASS | SCHEMA } [ , isolation_level INSTANCES ] ]

_ isolation_level [ INSTANCES [ , isolation_level { CLASS | SCHEMA } ] ]

_ variable

isolation_level:

_ REPEATABLE READ

_ READ COMMITTED

_ READ UNCOMMITTED

Example 1

vi $CUBRID/conf/cubrid.conf

isolation_level = 1

 

or

 

isolation_level = "TRAN_COMMIT_CLASS_UNCOMMIT_INSTANCE"

Example 2

csql> SET TRANSACTION ISOLATION LEVEL 1;

csql> ;xr

 

or

 

csql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED CLASS,READ UNCOMMITTED INSTANCES;

csql> ;xr

The following table shows the isolation levels from 1 to 6. It consists of table schema (row) and isolation level. For the unsupported isolation level, see Unsupported Combination of Isolation Level.

Levels of Isolation Supported by CUBRID

Name

Description

SERIALIZABLE (6)

In this isolation level, problems concerning concurrency (e.g. dirty read, non-repeatable read, phantom read, etc.) do not occur.

REPEATABLE READ CLASS with
REPEATABLE READ INSTANCES
(5)

Another transaction T2 cannot update the schema of table A while transaction T1 is viewing table A.
Transaction T1 may experience phantom read for the record R that was inserted by another transaction T2 when it is repeatedly retrieving a specific record.

REPEATABLE READ CLASS with
READ COMMITTED INSTANCES

(or CURSOR STABILITY) (4)

Another transaction T2 cannot update the schema of table A while transaction T1 is viewing table A.
Transaction T1 may experience R read (non-repeatable read) that was updated and committed by another transaction T2 when it is repeatedly retrieving the record R.

REPEATABLE READ CLASS with
READ UNCOMMITTED INSTANCES
(3)

Default isolation level.
Another transaction T2 cannot update the schema of table A  while transaction T1 is viewing table A.
Transaction T1 may experience R' read (dirty read) for the record that was updated but not committed by another transaction T2.

READ COMMITTED CLASS with
READ COMMITTED INSTANCES
(2)

Transaction T1 may experience A' read (non-repeatable read) for the table that was updated and committed by another transaction  T2 while it is viewing table A repeatedly.
Transaction T1 may experience R' read (non-repeatable read) for the record that was updated and committed by another transaction T2 while it is retrieving the record R   repeatedly.

READ COMMITTED CLASS with
READ UNCOMMITTED INSTANCES
(1)

Transaction T1 may experience A' read (non-repeatable read) for the table that was updated and committed by another transaction T2 while it is repeatedly viewing table A.
Transaction T1 may experience R' read (dirty read) for the record that was updated but not committed by another transaction T2.

If the transaction level is changed in an application client while a transaction is executed, the new level is applied to the rest of the transaction being executed. Therefore, some object locks that have already been obtained may be released during the transaction while the new isolation level is applied. For this reason, it is recommended that the transaction isolation level be modified when the transaction starts (after commit, rollback or system restart) because an isolation level which has already been set does not apply to the entire transaction, but can be changed during the transaction.