Database Concurrency

If there are multiple users with read and write privileges in a database, possibility exists that more than one user will access the database simultaneously. Controlling access and update in multi-user environment is essential to protect database integrity and ensure that users and transactions should have accurate and consistent data. Without appropriate control, data could be updated incorrectly in the wrong order.

Like most commercial database systems, CUBRID adopts serializability, an element that is essential to maintaining data concurrency within the database. Serializability ensures no interference between transactions when multiple transactions are executed at the same time. It is guaranteed more with the higher isolation level. This principle is based on the assumption that database consistency is guaranteed as long as transaction is executed automatically. This will be covered in the Lock Protocol section in detail.

The transaction must ensure database concurrency, and each transaction must guarantee appropriate results. When multiple transactions are being executed at the same time, an event in transaction T1 should not affect an event in transaction T2. This means isolation. Transaction isolation level is the degree to which a transaction is separated from all other concurrent transactions. The higher isolation level means the lower interference from other transactions. The lower isolation level means the higher the concurrency. A database determines whether which lock is applied to tables and records based on these isolation levels. Therefore, can control the level of consistency and concurrency specific to a service by setting appropriate isolation level.

You can set an isolation level by using the SET TRANSACTION ISOLATION LEVEL  statement or system parameters provided by CUBRID. For more information, see Concurrency/Lock Parameters.

The read operations that allow interference between transactions with isolation levels are as follows:

• Dirty read : A transaction T2 can read D' before a transaction T1 updates data D to D' and commits it.
• Non-repeatable read : A transaction T1 can read other value, if a transaction T2 updates data while data is retrieved in the transaction T2 multiple times.
• Phantom read : A transaction T1 can read E, if a transaction T2 inserts new record E while data is retrieved in the transaction T1 multiple times.

CUBRID ensures lock/unlock in the unit of row, index, table, or database, and it provides six levels of transaction isolation. Therefore, you can adjust concurrency levels more specifically than other DBMSs.

Isolation Levels Provided by CUBRID