Concurrency control is based on the application requirements. Some applications need to execute as if they have exclusive ownership of the database. Other applications can tolerate some degree of interference from other applications that run simultaneously.
To meet the needs of different applications, the SQL-92 standard defines four levels of isolation for transactions. However, by design, solidDB does not support the ‘Read Uncommitted’ isolation level as this level cannot guarantee database integrity or that transactions have a consistent view of data; solidDB transactions can only read committed data.
The following isolation levels are supported:
▪ READ COMMITTED: The view of the database might change in the middle of a transaction as a result of other transactions committing their changes.
▪ REPEATABLE READ: The data that is read by a transaction is guaranteed not to be updated or deleted (by another transaction) until the transaction terminates but, if the transaction re-executes the query, additional rows (added by other transactions) might be returned.
When you use 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 phantom updates might occur.
▪ SERIALIZABLE: A consistent view of the database is guaranteed. The data that is read by a transaction is guaranteed not to change or be added to (by another transaction) until the transaction terminates.
solidDB can provide serializable transactions by detecting conflicts between transactions. Conflicting transactions are detected by using both write-set and read-set validations.
Note The SERIALIZABLE isolation level is available for disk-based tables only.