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

This isolation level does not allow a transaction to read an object being modified by another transaction. It also does not allow another transaction to change the table (part of schema) accessed by the transaction. However, the transaction might not be able to read a record twice (non-repeatable read). This means that the transaction may read two different values if it reads the same object twice. This can happen because another transaction may start, change and commit the object between the two reads. Similarly, the results can be different if a single query is executed twice. The difference in the results is caused by another transaction inserting, updating or deleting records committed between the execution of the two queries.

You can use the CURSOR STABILITY keyword as another name of the isolation level when you execute the SET TRANSACTION statement.

The following are the rules of this isolation level:

  • A transaction does not overwrite an object being modified by another transaction.
  • A transaction does not commit an object until the end of the transaction.
  • A transaction does not read an object being modified by another transaction.
  • Another transaction does not modify a table being read or changed by the current transaction until the end of the current transaction.

This isolation level uses a two-phase locking protocol for an exclusive lock. However, a shared lock on a record is released right after it is read. An intention lock on a table is released at the end of the transaction because schema reads are repeatable. On the initial installation of CUBRID, the isolation level set in cubrid.conf is REPEATABLE READ CLASS, READ UNCOMMITTED INSTANCES. If an isolation level is not specified, this will be the default isolation level.

Example

One transaction inserts or deletes data for a table while another transaction queries the table in various aspects. Transaction T1 has an optional isolation level, and transaction T2 has the REPEATABLE READ CLASS with READ COMMITTED INSTANCES isolation level. In this example, it is assumed that the participant2 table was not created earlier.

  1. Transaction T1 creates the participant2 table and inserts a record into the table. As soon as transaction T1 commits, CUBRID releases the lock obtained by transaction T1. Then transaction T2 obtains the lock on the participant2 table. The lock on the record is released after the query is executed, and another transaction is permitted to modify the record.
    • User1 (T1):
    • SET TRANSACTION ISOLATION LEVEL REPEATABLE READ CLASS, READ COMMITTED INSTANCES;
      ;xrun
      ;autocommit off
      create table participant2 (host_year integer, nation_code char(3));
      insert into participant2 (host_year, nation_code) values (2008, 'AUS');
      commit work;
      ;xrun
    • User2 (T2):
    • SET TRANSACTION ISOLATION LEVEL REPEATABLE READ CLASS, READ COMMITTED INSTANCES;
      ;xrun
      ;autocommit off
      select * from participant2;
      ;xrun
  2. Transaction T1 inserts another record into the participant2 table. When transaction T2 tries to query the participant2 table, it stands by the query execution because transaction T1 is trying to insert another record into the table without releasing the lock. Transaction T2 can see only committed values, so it has to wait until transaction T1 commits.
    • User1 (T1):
    • insert into participant2 (host_year, nation_code) values (2000, 'NED');
      ;xrun
    • User2 (T2):
    • select host_year, nation_code from participant2;
      ;xrun
  3. Once transaction T1 inserts another record into the participant2 table and commits, locks are released. Transaction T2 resumes and gets the query result. The participant2 table includes the record committed by the current transaction T1. Note that in the same transaction, the SELECT statement may return a different result from the first query result (non-repeatable read on a given record).
    • User1 (T1):
    • insert into participant2 (host_year, nation_code) values (2004, 'AUS');
      commit work;
      ;xrun
    • User2 (T2):
    • === <Result of SELECT Command in Line 1> ===
          host_year  nation_code
      =====================================
              2008  'AUS'
              2000  'NED'
              2004  'AUS'
      3 rows selected
      .
  4. Transaction T1 inserts another record into the participant2 table. Transaction T2 tries to query the participant2 table again, but stands by because transaction T1 is inserting another record into the table.
    • User1 (T1):
    • insert into participant2 (host_year, nation_code) values (1994, 'FRA');
      ;xrun
    • User2 (T2):
    • select host_year, nation_code from participant2;
      ;xrun
  5. Transaction T1 deletes the first record (2008, 'AUS') of the participant2 table and commits without releasing the lock. Transaction T2 resumes and sees the new record as well as some records inserted earlier. However, the first record (2008, 'AUS') has been deleted.
    • User1 (T1):
    • delete from participant2 where host_year=2008 and nation_code='AUS';
      commit work;
      ;xrun
    • User2 (T2):
    • === <Result of SELECT Command in Line 1> ===
          host_year  nation_code
      ==========================
               2000  'NED'
               2004  'AUS'
               1994  'FRA'
      3 rows selected
      .
  6. Transaction T1 tries to change the name of the participants2 table to nation_medals, but stands by because transaction T2 holds a lock on the table (schema is repeatable). Transaction T2 queries the table again, commits and releases the lock. Transaction T2 tries to query the table again, but stands by because transaction T1 has not committed yet.
    • User1 (T1):
    • rename table participant2 as nation_medals;
      ;xrun
    • User2 (T2):
    • select host_year, nation_code from participant2;
      ;xrun
      === <Result of SELECT Command in Line 1> ===
          host_year  nation_code
      ===================================
               2000  'NED'
               2004  'AUS'
               1994  'FRA'
       

      commit work;
      select * from participant2;
      ;xrun
  7. Transaction T1 commits the operation and releases the lock, then transaction T2 resumes. However, the query fails because the name of T2 has been changed to nation_medals. Then, transaction T2 gets a syntax error saying that the participant2 table does not exist any more.
    • User1 (T1):
    • commit work;
      ;xrun
      1 command(s) successfully processed.
    • User2 (T2):
    • In line 2, column 16,
      ERROR: Class participant2 does not exist
      .