With the main concepts in mind, we can outline the behavior of each isolation level and assess its impact on anomalies, performance, and the need for additional code. SQL databases should provide, at a minimum, Serializable and Read Committed levels. This is because applications have the flexibility to implement retry logic or use explicit locking to ensure consistency. Failure to implement one of these options could undermine the scalability of the application.
The gold standard is Serializable isolation level where the developer does not have to think about concurrent access, and all anomalies are detected and prevented by the database.
Serializing all transactions to run one after the other can solve all problems without any anomalies possible and without requiring additional code. However, this approach can negatively impact performance and scalability as all objects accessed by the transaction would have to be locked. For this reason, this approach is almost never used in practice.
The Serializable isolation level does not actually serialize transactions but instead checks that the end result is the same as if the transactions were serialized. This isolation level in MVCC databases prevents all anomalies without significantly impacting performance. However, it has one drawback: it can fail and raise a serializable error if it detects conflicting orders in which the reads and writes occurred. In such cases, the transaction cannot be committed to avoid a state that could not have occurred with serialized transactions. It is the responsibility of the application to retry the transaction to ensure that it is eventually executed successfully.
It's worth noting that Oracle Database does not offer a Serializable isolation level. You can set "serializable" but Oracle does not prevent all anomalies (see an example), as the name is derived from the initial SQL-92 specifications that did not account for certain write skew anomalies. Previously, Oracle implemented a serialization feature that locked all transaction tables, but this feature has since been removed.
PostgreSQL offers Serializable Snapshot Isolation (SSI), which detects conflicts at commit without locking. It looks at predicates to lock ranges, potentially resulting in false positives.
To ensure serializability, MySQL locks rows and predicates. It's worth noting that predicate or range locking is complicated but essential since the database must lock not only existing rows but also potential insertions to avoid anomalies. Without predicate or range locking, locking the entire table would be necessary, which is not scalable.
In YugabyteDB, the Serializable isolation level is implemented by recording all read and write intents. These intents are stored in the IntentsDB LSM-Tree of the corresponding table or index along with the provisional records for transaction writes. This allows for conflicts to be detected as soon as possible. The intents persist until the end of the transaction and only the write provisional records are applied to the RegularDB LSM-Tree at commit.
Serializable is rarely used in non-MVCC databases, like DB2 or SQL Server, because it requires acquiring a shared lock on all ranges that are read and it blocks concurrent DML operations.
According to the SQL Standard, Serializable should be the default isolation level. However, in practice, no database sets it as the default due to the errors it can introduce. One of the reasons behind this is that the main traditional commercial databases did not provide a good implementation for Serializable. For instance, Oracle used MVCC but never added serializability on top of it, and DB2 and SQL Server did not implement MVCC from their early versions. As a result, to avoid unexpected runtime surprises, Read Committed isolation is still widely used on modern databases, especially while migrating from the databases above.
Note that when relying on additional read locks or intents for anomaly detection, all transactions must run as Serializable, not just those interested in this isolation.
Here are the characteristics of the Serializable isolation level in YugabyteDB
- Read time: the start of the transaction
- Possible anomalies: none, they are detected at write time
- Performance: more provisional records are written
- Development constraint: the application must catch serializable errors and implement a retry logic. It must also ensure that all transactions touching the same tables are serializable.
On Friday I'll talk about this retry logic to implement in the application:
Serializable Errors and Retry Logic | YugabyteDB Friday Tech Talk | LinkedIn
Join YugabyteDB Developer Advocate Franck Pachot and Co-Founder Kannan Muthukkaruppan as they discuss how applications should handle retriable errors due to serializable conflicts or clock skew uncertainty, and how to code a retry logic.
linkedin.com
The following posts in this series will describe the lower isolation level, as well as a special case of Serializable for read-only transactions.
Top comments (0)