In databases that use multi-version concurrency control (MVCC), the Snapshot Isolation (SI) technique is used as the basis for read-only and serializable isolation levels. SI helps prevent most anomalies and phantom reads, but it does not protect against write skew. In order to handle write skew, the Serializable isolation level requires additional conflict detection. SI aligns with the SQL-92 specification for Repeatable Read isolation level and offers additional protection against phantom reads.
Write skew happens when two transactions read certain rows, make decisions based on the results, and then write to rows that affect the original read results. If the writes of these transactions don't conflict with each other, meaning they don't write to the same row, they won't block each other. However, because one write changes the state that was initially read, it makes the decision of the other transaction incorrect.
To illustrate this, consider a scenario where two human resources managers are instructed to award a bonus to a random employee for each team where no bonuses have been given yet. If both managers query the system at the same time, they may award the bonus to different employees from the same team. Two bonuses have been granted to the same team when both transactions are committed. While such cases are rare in practice, they can be avoided by writing some information to a common record so that the two transactions cannot overlap.
Snapshot Isolation in MVCC databases corresponds to the Repeatable Read isolation level but also safeguards against another anomaly not mandated by this level, according to the SQL specification: phantom reads. It is worth noting that Oracle highest isolation level is Snapshot Isolation, even if set with SERIALIZABLE, due to their interpretation of SQL92 definitions when implementing non-blocking reads with MVCC. To achieve a true ANSI SQL Serializable isolation level, more locking was required with a serializable=true non-default parameter. However, this approach is now deprecated as you can explicitly LOCK TABLE if needed.
Here are the characteristics of the Snapshot Isolation isolation level in YugabyteDB, which you get by setting Repeatable Read isolation level (also with Read Committed when --yb_enable_read_committed_isolation=false)
- Read time: the start of the transaction
- Possible anomalies: write skew
- Performance: no overhead, uses MVCC
- Development constraint: the application must catch serializable errors and implement a retry logic. Write skew situations must be managed by the application with implicit locking or having the transaction logic in a single SQL statement.
Top comments (0)