Overview
Database transaction isolation levels are mechanisms that control the degree of interaction between concurrent transactions. The core objective is to balance concurrency performance while ensuring data consistency. The SQL standard defines four isolation levels, each addressing different data anomalies.
Description of Data Anomaly Types
Dirty read: A transaction reads data modified by another uncommitted transaction.
Non-repeatable read: Within the same transaction, reading the same data multiple times yields inconsistent results (the data has been modified by other committed transactions).
Phantom read: Within the same transaction, multiple queries with identical conditions return inconsistent result sets (due to other committed transactions inserting or deleting data).
Isolation Level Comparison Table
|
Read uncommitted (Read Uncommitted) | Possible | Possible | Possible | Generally not recommended for use. |
Read committed (Read Committed) | Impossible | Possible | Possible | Read/write separation, high real-time requirements |
Repeatable Read (Repeatable Read) | Impossible | Impossible | Possible (but MySQL InnoDB and TDSQL Boundless have largely resolved it) | default level, complex business logic |
Serializable (Serializable) | Impossible | Impossible | Impossible | Strong consistency requirement |
Analysis of Isolation Levels Behavior for TDSQL Boundless
TDSQL Boundless supports only Read Committed (Read Committed) and Repeatable Read (Repeatable Read) isolation levels. The default isolation level is Repeatable Read.
Repeatable Read Level
TDSQL Boundless fully supports the Repeatable Read isolation level, with behavior highly similar to MySQL. More precisely, TDSQL Boundless supports the snapshot isolation level, which does not exhibit phantom read exceptions but may still encounter write skew exceptions.
Snapshot obtaining method: When a transaction reads for the first time, it creates a consistent view (snapshot). Subsequent read operations will be based on this snapshot, thereby ensuring that the data read within this transaction is consistent.
Read Committed Level
TDSQL Boundless also supports the Read Committed level, but its implementation differs significantly from MySQL's.
Snapshot Acquisition Method: At this level, TDSQL Boundless reacquires the latest committed data snapshot for each query statement. That is, read operations within a single statement occur under the same snapshot, but read operations across multiple statements occur under different snapshots.
Key Differences in Locking Behavior: In TDSQL Boundless, certain pessimistic locking behaviors at the Read Committed level resemble those at the Repeatable Read level, differing from MySQL's implementation of Read Committed.
In MySQL's Read Committed level: For current reads such as SELECT ... FOR UPDATE, it generally only locks existing records matching the query and does not apply gap locks (Gap Lock), to reduce lock conflicts.
Under TDSQL Boundless's Read Committed level: To ensure strict consistency in distributed environments, it still employs range locks. This means that even at the Read Committed level, a transaction performing range queries with locks may still prevent other transactions from inserting data into that range, a behavior similar to its Repeatable Read level.