Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

CUBRID Transaction and Lock

In multi-user environment, controlling access and update is essential to protect database integrity and ensure that a user’s transaction will have accurate and consistent data. Without appropriate control, data could be updated incorrectly in the wrong order. 

To control parallel operations on the same data, data must be locked during transaction, and unacceptable access to the data by another transaction must be blocked until the end of the transaction. In addition, any updates to a certain class must not be seen by other users before they are committed. If updates are not committed, all queries entered after the last commit or rollback of the update can be invalidated.

All examples introduced here were executed by csql.

LOCK

When a single transaction requires a resource, the database carries out tasks after occupying the lock in the corresponding resource. When a different transaction requires the same resource, the transaction will hold the lock until it is released. The image below illustrates such a locking; when transaction 1 occupies and uses the resource, that resource will be locked. Transaction 2 will enter stand-by mode when it requests the resource that is locked by transaction 1 (This is called Live Lock, and such an action is called blocking).

Transaction Lock

Live Lock is also a normal situation that can occur while running a database. However, if this kind of lock continues for an extended period of time, it will have negative effects on applications since other transactions have to be in stand-by mode; therefore, it is important to make sure to keep the time of the transaction lock to a minimum.

CUBRID Locking Protocol

In the two-phase locking protocol used by CUBRID, a transaction will have a shared lock obtained before reading and an exclusive lock obtained before writing in order to keep the conflicting operations from being simultaneously executed.

Types of CUBRID locking and determining permissions.

Lock compatibility

Lock Holder
NULL_LOCK IS_LOCK S_LOCK IX_LOCK SIX_LOCK U_LOCK X_LOCK
Lock Requester NULL_LOCK TRUE TRUE TRUE TRUE TRUE TRUE TRUE
IS_LOCK TRUE TRUE TRUE TRUE TRUE N/A FALSE
S_LOCK TRUE TRUE TRUE FALSE FALSE FALSE FALSE
IX_LOCK TRUE TRUE FALSE TRUE FALSE N/A FALSE
SIX_LOCK TRUE TRUE FALSE FALSE FALSE N/A FALSE
U_LOCK TRUE N/A TRUE N/A N/A FALSE FALSE
X_LOCK TRUE FALSE FALSE FALSE FALSE FALSE FALSE

S_LOCK & IS_LOCK

S_LOCK and IS_LOCK prevent the data from being read. The following image is an example of how to obtain the shared lock when it reads the data.

obtaining-shared-locks.png

It shows that the lock is configured to read the cub table B record; it allows to obtain IS_LOCK (intention share lock) on the CUB table first, and read query data to S_LOCK (shared lock) with the B record value. S_LOCK will be released when the read data is committed.

S_LOCK (Shared Lock)

S_LOCK (Shared Lock), which is the lowest level of the locks detects S_Lock when it reads the data. S_LOCK is compatible with a different S_LOCK. That is, it is allowed to retrieve the same data simultaneously from different transactions, though it is not allowed to update or delete the data.

IS_LOCK (Intent Shared Lock)

When IS_LOCK (Intent Shared Lock) reads data, the table preceding the request of S_LOCK on the corresponding record will request IS_LOCK. To apply IS_LOCK at a table level first allows other transactions to obtain the shared/exclusive lock other than the corresponding record. (For more details: See "INTENT LOCK")

Example 1: Open two terminal (cmd) windows and connect terminal 1 to the demodb database by csql, then execute the following queries. In terminal 2, check the lock of the demodb database by using the cubrid lockdb command. (For more details on the cubrid lockdb command: See "Cubrid Manual.")

Note: All of the examples in this document will be carried out in READ UNCOMMITTED INSTANCES only, which is the default isolation level of CUBRID.

Terminal (cmd) 1.

$> csql ?u dba --no-auto-commit demodb
csql> SELECT * FROM cub WHERE a=3 USING INDEX i_cub_a(+)
csql> ;x

Terminal (cmd) 2.

C:> cubrid lockdb demodb
             Omitted
OID =  0|   287|   8 ? (The internal address value of the cub table)
Object type: Class = cub. (The cub table that obtained IS_LOCK)
Total mode of holders =   IS_LOCK, Total mode of waiters = NULL_LOCK.
Num holders=  1, Num blocked-holders=  0, Num waiters=  0
LOCK HOLDERS:
    Tran_index =   1, Granted_mode =  IS_LOCK, Count =   2, Nsubgranules =  0

Result: It shows that IS_LOCK was configured in the cub table, and the a=3 record value has been read. Here, S_LOCK cannot be seen to read the a=3 record since READ UNCOMMITTED INSTANCES, the default transaction isolation level of CUBRID, did not configure S_LOCK when it read some records.

Terminal (cmd) 1.

csql>;commit
csql>;ex

X_LOCK & IX_LOCK

Configures (or obtains) X_LOCK and IX_LOCK when writing data. The following image is an example of how to obtain the exclusive lock when it inputs or updates the data.

obtain-exclusive-lock.png

It shows the configuration of the lock for updating the B record in the CUB table; it obtained IX_LOCK (Intent Exclusive/Exclusive Lock) in the CUB table before updating data by obtaining X_LOCK (Exclusive/Single Lock) in the B record value.

X_LOCK (Exclusive Lock)

X_LOCK (Exclusive Lock) can be obtained at the time of writing; it is not compatible with any other locks and is maintained until the end of a transaction (before commits). Like the table above, X_LOCK is not compatible with any other locks.

IX_LOCK (Intent Exclusive Lock)

IX_LOCK (Intent Exclusive Lock) is requested in the corresponding table level before X-LOCK is requested of the record (Row) during the writing. Applying IX_LOCK (Intent Exclusive Lock) at the table level first allows transactions other than the corresponding record (Row) to obtain the shared or exclusive lock, and thus it can increase concurrency in the process of the transactions. (For more information, see "INTENT LOCK".)

Example 1: Opening two terminal (cmd) windows, connecting terminal 1 to the demodb database by csql, then executing the following queries. In terminal 2, check the lock of the demodb database by using the cubrid lockdb command.

Note: All of the examples in this document will be carried out in READ UNCOMMITTED INSTANCES only, which is the default isolation level of CUBRID.

Terminal (cmd) 1.

$>csql ?u dba --no-auto-commit demodb
csql> DELETE FROM cub WHERE a=3 USING INDEX i_cub_a(+)
csql>;x

Terminal (cmd) 2.

C:>cubrid lockdb demodb
             Omitted
OID =  0|   287|   8 ? (The internal address value of the cub table)
Object type: Class = cub. (The cub table that obtained IX_LOCK)
Total mode of holders =   IX_LOCK, Total mode of waiters = NULL_LOCK.
Num holders=  1, Num blocked-holders=  0, Num waiters=  0
LOCK HOLDERS:
    Tran_index =   1, Granted_mode =  IX_LOCK, Count =   3, Nsubgranules =  2
 
OID =  0|  1800|   3 ? (The internal address value of the record a=3)
Object type: Instance of class ( 0|   287|   8) = cub (The a=3 record that obtained X_LOCK)
Total mode of holders =    X_LOCK, Total mode of waiters = NULL_LOCK.
Num holders=  1, Num blocked-holders=  0, Num waiters=  0
LOCK HOLDERS:
    Tran_index =   1, Granted_mode =   X_LOCK, Count =   1
 
OID =  0|  1800|   4 ? (The internal address value of the record a=4)
Object type: Instance of class ( 0|   287|   8) = cub (Obtained X-LOCK up to the a=4 record, the next value of the a=3 record)
Total mode of holders =   NX_LOCK, Total mode of waiters = NULL_LOCK.
Num holders=  1, Num blocked-holders=  0, Num waiters=  0
LOCK HOLDERS:
    Tran_index =   1, Granted_mode =  X_LOCK, Count =   1

Result: It can be seen that X_LOCK (Intent Exclusive/Single Lock) is configured in the cub table, and X_LOCK (Exclusive/Single Lock) in the a=3 record is configured and deleted. There is an additional configuration of X_LOCK in this record; in order to delete the a=3 record, an operator needs to obtain this additional configuration and X-LOCK, the record next to the deleted record of the internal DB, together. The operation protects the data from the tasks performed by other transactions or the rollback of the deleted records.

Terminal (cmd) 1.

csql>;commit
csql>;ex

INTENT LOCK

In order to understand IS_LOCK and IX_LOCK, it is necessary to first understand the intent lock. The intent lock is supposed to be configured in a table, not a record (Row). If the intent lock is configured in a table, other transactions cannot obtain any exclusive lock from the corresponding record (Row) or pages. In order to understand the intent lock precisely, it is necessary to first comprehend the size of the locks, which defines their range. 

The intent lock requires IS_LOCK in the corresponding table level before the request of the S_LOCK that is for the pages or rows within the table. Once the intent lock is configured in a table as described above, the database engine checks the table intent lock to see whether a transaction can obtain the requested lock by checking the contents of the intent lock so that it can determine the configuration of the lock, and therefore the processing cost of the locks can be reduced. Suppose that a transaction is locking a certain table record (Row), then another transaction is trying to lock the entire table including the record (Row) of the configured lock. The transaction that has previously locked cannot perform any task further in the condition of the record (Row) lock, and thus the transaction that configured the table lock has to be unlocked in order to release another. To prevent this from happening, the transaction applies Intent LOCK to the table that holds the record (Row) that is requested to be locked. This allows other transactions to check whether the table is already locked before attempting lock.

CUBRID Transaction

To control parallel operations on the same data, CUBRID locks data during the transaction, and does not accept any access of data that is unapproved from other transactions until the transaction ends. Access methods to the transaction data may cause Dirty Read, Non-Repeatable Read, and Phantom Read; CUBRID controls a table and record Read with the transaction isolation level.

Transaction Concurrency

When multiple transactions simultaneously attempt to read or write to the same data, a Dirty Read, Non-Repeatable Read, or Phantom Read may occur. To maintain data consistency and integrity, a database controls the data interruption rate among transactions according to their isolation levels  (A high isolation level improves data consistency but reduces concurrency, as more locks need to be maintained for a longer period of time).

Dirty Read

Dirty Read is when the data that has been modified by a transaction but not yet committed is being read by other transactions.

"Dirty Read refers to the reflect in a transaction even though it is not committed. When the data rolls back, transactions that have the data are considered as having Dirty Data."

Example 1: Open two terminal (cmd) windows and connect to the demodb database using csql, then execute the following queries.

Terminal (cmd) 1.

$>csql -u dba --no-auto-commit demodb
csql> SELECT * FROM cub WHERE a <= 3 USING INDEX i_cub_a(+)
csql> ;x
            a  b
===================================
            1  'aaa'
            2  'bbb'
            3  'ccc'

Terminal (cmd) 2.

$>csql ?u dba ?-no-auto-commit demodb
csql>;set level 513
csql>;co
csql> UPDATE cub SET b='zzz' where a=2 USING INDEX i_cub_a(+)
csql>;x ?Do not commit.
 
Terminal (cmd) 1.
csql> SELECT * FROM cub WHERE a <= 3 USING INDEX i_cub_a(+)
csql> ;x
            a  b
===================================
            1  'aaa'
            2  'zzz'
            3  'ccc'

Result: Although transaction 2 (terminal 2) is not committed after changing column b of the cub table to, bbb?zzz, transaction 1 (terminal 1) shows the uncommitted ‘zzz’ value. If transaction 2 (terminal 2) rolls back, the transaction (terminal 1) will have Dirty Data which is ‘zzz.’

Non-Repeatable Read

A transaction reading the same object several times may erroneously read a different object. For example, if transaction T2 deletes, updates, or modifies the data record that has been retrieved by T1, and then commits it while T1 was rereading the search query without committing it, T1 will retrieve the same record with a different value.

A Non-repeatable read is an instance in which the results of two consecutive queries executed in the same transaction are different from each other because another transaction has modified or deleted the value being read between the two queries. Put simply, it is an instance in which a transaction reads missing or modified data.

Example 1: Change transaction isolation level to READ COMMITTED INSTANCES and execute queries in the following sequence:

Terminal (cmd) 1.

csql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ CLASS, READ COMMITTED INSTANCES;
csql> ;x
csql> ;co
csql> SELECT * FROM cub WHERE a <= 3 USING INDEX i_cub_a(+)
csql> ;x
            a  b
===================================
            1  'aaa'
            2  'bbb'
            3  'ccc'

Terminal (cmd) 2.

csql> SET TRANSACTION ISOLATION LEVEL REPEATABLE READ CLASS, READ COMMITTED INSTANCES;
csql> ;x
csql> ;co
csql> UPDATE cub SET b='zzz' where a=2 USING INDEX i_cub_a(+)
csql>;x 
csql>;co ? If it is not committed, the a=2 records of other transactions are configured as X_LOCK (Exclusive Lock), making them suspend the read/write action until the X_LOCK in the a=2 record is released.

Terminal (cmd) 1.

csql> SELECT * FROM cub WHERE a <= 3 USING INDEX i_cub_a(+)
csql> ;x ? If transaction 2 (terminal 2) does not commit, it will wait for the commitment.
a  b
===================================
            1  'aaa'
            2  'zzz'
            3  'ccc'

Result: Transaction T1 reads an object. Then, transaction T2 updates (deletes) the object and commits. If T1 attempts to read the object again, it will retrieve the changed content of that object (It may look for an object that no longer exists).

Example: Update (UPDATE cub SET b='bbb' WHERE a=2) the changed (b column bbb->zzz) data again and execute queries in the following sequence:

Terminal (cmd) 1.

csql> SELECT * FROM cub WHERE a <= 3 USING INDEX i_cub_a(+)
csql> ;x
            a  b
===================================
            1  'aaa'
            2  'bbb'
            3  'ccc'

Terminal (cmd) 2.

csql> DELETE FROM cub where a=2 USING INDEX i_cub_a(+)
csql> INSERT INTO cub VALUES(0,’xxx’)
csql>;x
csql>;co 

Terminal (cmd) 1.

csql> SELECT * FROM cub WHERE a <= 3 USING INDEX i_cub_a(+)
csql> ;x ? If T2 does not commit, it will wait for the commitment.
            a  b
===================================
0  'xxx'
            1  'aaa'
            2  'bbb'
            3  'ccc'

Result: Transaction T1 executes queries on table CUB. Then transaction T2 deletes and inserts several pieces of data of CUB, and commits. If T1 executes the same query again, it may retrieve different results due to the inserted/deleted objects.

Phantom Read

If a transaction retrieves a record multiple times with a search condition, it may retrieve a newly inserted object, which is called a phantom. That is, when a record in a certain range is read two or more times in a transaction, a record that was not in the first query may appear in the second query. This happens because the ‘insertion’ of a new record is allowed in the execution of a transaction.

“The only isolation level that does not allow other transactions to insert while reading is SERIALIZABLE."

Transaction Isolation Level

CUBRID specifies the isolation level of a transaction so that it is executed in a less restrictive consistency. The isolation level of a transaction is the degree of interruption by other concurrent transactions. The CUBRID Isolation level is determined by the combination of a table (schema) and a record.

SERIALIZABLE

Dirty Read, Non-Repeatable Read, and Phantom Read do not occur.

REPEATABLE READ CLASS with REPEATABLE READ INSTANCES

Dirty Read and Non-Repeatable Read do not occur, whereas Phantom Read may occur.

REPEATABLE READ CLASS with READ COMMITTED INSTANCES

Dirty Read does not occur, but Non-Repeatable Read and Phantom Read may occur.

REPEATABLE READ CLASS with READ UNCOMMITTED INSTANCES

Dirty Read, Non-Repeatable Read, and Phantom Read may occur.

Note: The isolation levels of CUBRID default($CUBRID/conf/cubrid.conf) are REPEATABLE READ CLASS and READ UNCOMMITTED INSTANCES. The REPEATABLE READ CLASS table and the READ COMMITTED CLASS table are different in that they use different table read configurations when changing a table (schema). It is not recommended to configure it to READ COMMITTED CLASS, as a table change (update) usually happens in after hours.

INSTANCE Dirty Read Repeatable Read Phantom Read
Read uncommittedPossiblePossiblePossible
Read committedNot PossiblePossiblePossible
Non-Repeatable readNot PossibleNot PossiblePossible
SerializableNot PossibleNot PossibleNot Possible

TRANSACTION ISOLATION LEVEL and LOCK TEST

Compare each configured LOCK when Select, Insert, Update, and Delete are performed in "REPEATABLE READ CLASS, READ UNCOMMITTED INSTANCES," the default transaction isolation level of CUBRID, and check when the S_LOCK and X_LOCK are released.

"CUBRID LOCK can be checked through the cubrid lockdb DB_name command."

Scenario 1 (Inquiry)

Data inquiry query: SELECT * FROM cub WHERE a = 3 USING INDEX i_cub_a(+)

OID =  0|   287|   8
Object type: Class = cub.
Total mode of holders =   IS_LOCK, Total mode of waiters = NULL_LOCK.
Num holders=  1, Num blocked-holders=  0, Num waiters=  0
LOCK HOLDERS:
    Tran_index =   1, Granted_mode =  IS_LOCK, Count =   2, Nsubgranules =  0
? The reason why S_LOCK is missing is that in READ UNCOMMITTED INSTANCES, the default transaction isolation level of CUBRID, data are read without obtaining S_LOCK. That is, Dirty Read occurs in READ UNCOMMITTED INSTANCES, the transaction isolation level, as it reads data without applying S_LOCK.

Scenario 2 (Input)

Data input query: INSERT INTO cub VALUES(10,'jjj')

OID =  0|   287|   8
Object type: Class = cub.
Total mode of holders =   IX_LOCK, Total mode of waiters = NULL_LOCK.
Num holders=  1, Num blocked-holders=  0, Num waiters=  0
LOCK HOLDERS:
    Tran_index =   1, Granted_mode =  IX_LOCK, Count =   1, Nsubgranules =  1
 
OID =  0|  1800|  10
Object type: Instance of class ( 0|   287|   8) = cub.
Total mode of holders =    X_LOCK, Total mode of waiters = NULL_LOCK.
Num holders=  1, Num blocked-holders=  0, Num waiters=  0
LOCK HOLDERS:
Tran_index =   1, Granted_mode =   X_LOCK, Count =   1
? Obtain IX_LOCK in the cub table for input (update), and input (update) by acquiring “OID =  0|  1800|  10” (DB address value) X_LOCK (Exclusive Lock). If other transactions read data before the transaction is committed, CUBRID will Dirty Read uncommitted transactions.

Scenario 3 (Update)

Data Update Query: UPDATE cub SET b='zzz' (DELETE FROM cub) WHERE a=3 USING INDEX i_cub_a(+)

OID =  0|   287|   8
Object type: Class = cub.
Total mode of holders =   IX_LOCK, Total mode of waiters = NULL_LOCK.
Num holders=  1, Num blocked-holders=  0, Num waiters=  0
LOCK HOLDERS:
    Tran_index =   1, Granted_mode =  IX_LOCK, Count =   3, Nsubgranules =  2
 
OID =  0|  1800|   4
Object type: Instance of class ( 0|   287|   8) = cub.
Total mode of holders =    X_LOCK, Total mode of waiters = NULL_LOCK.
Num holders=  1, Num blocked-holders=  0, Num waiters=  0
LOCK HOLDERS:
Tran_index =   1, Granted_mode =   X_LOCK, Count =   2
 
OID =  0|  1800|   3
Object type: Instance of class ( 0|   287|   8) = cub.
Total mode of holders =    X_LOCK, Total mode of waiters = NULL_LOCK.
Num holders=  1, Num blocked-holders=  0, Num waiters=  0
LOCK HOLDERS:
Tran_index =   1, Granted_mode =   X_LOCK, Count =   2

You can see that there is one more setting X-LOCK (Exclusive/Single Lock) for updating (deleting) one record. To update (delete) record a=3, DB internally obtains up to the records next to deleted records, including X-LOCK and delete. This measure aims to protect data from tasks by other transactions or the rollback of deleted records.




You are either using a very old browser or a browser that is not supported.
In order to browse cubrid.org you need to have one of the following browsers:



Internet Explorer: Mozilla Firefox: Google Chrome: