Concurrency control is based on the application requirements. Some applications need to execute as if they had exclusive ownership of the database. Other applications can tolerate some degree of interference from other applications running simultaneously. To meet the needs of different applications, the SQL-92 standard defines four levels of isolation for transactions. By principle, solidDB® cannot read uncommitted data. The reason is that it sacrifices the consistent view and potentially also database integrity.
The supported isolation levels are explained below.
Read Committed
This isolation level allows a transaction to read only committed data. Nonetheless, the view of the database may change in the middle of a transaction when other transactions commit their changes.
Repeatable Read
This isolation level allows a transaction to read only committed data and guarantees that read data will not change until the transaction terminates. solidDB® additionally ensures that the transaction sees a consistent view of the database. When using optimistic concurrency control, conflicts between transactions are detected by using transaction write-set validation. This means that the server validates only write operations, not read operations. For example, if a transaction involves one read and one update, solidDB® validates that no one has updated the same row in between the read operation and the update operation. In this way, lost updates are detected, but the read is not validated. With transaction write-set validation, phantom updates may occur and transactions are not serializable.
Serializable
This isolation level allows a transaction to read only committed data with a consistent view of the database. Additionally, no other transaction may change the values read by the transaction before it is committed because otherwise the execution of transactions cannot be serialized in the general case.
solidDB® can provide serializable transactions by detecting conflicts between transactions. Conflicting transactions are detected by using both write-set and read-set validations. Because no locks are used, all concurrency control anomalies are avoided, including the phantom updates. This feature is enabled by using the command
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
The SERIALIZABLE isolation level is available for disk-based tables only.