Isolation levels play a crucial role in maintaining data integrity and consistency in database systems. In TiDB, a distributed SQL database, understanding isolation levels is essential for designing robust and efficient applications. Let's break down isolation levels in TiDB in a simple and easy-to-understand manner.

Isolation Levels in TiDB

Isolation levels determine how transactions interact with each other in a database environment. They control the visibility of changes made by one transaction to other transactions. TiDB offers various isolation levels to cater to different application requirements and concurrency scenarios.

Serializable

This isolation level is the highest in TiDB, ensuring that transactions are executed as if they were processed sequentially, eliminating all read anomalies. It is ideal for scenarios where data consistency and integrity are paramount, even though there may be a potential performance trade-off.

Example Scenario: Money Transfer

In this example scenario, we consider a banking application where users can transfer money between accounts. Let's explore how each isolation level behaves in the context of concurrent transactions.

Imagine we have two bank accounts: Account A with a balance of $500 and Account B with a balance of $700.

In the Serializable isolation level, two transactions occur concurrently.

• Transaction 1 transfers $200 from Account A to Account B, and Transaction 2 transfers $100 from Account A to Account B.
• Regardless of the order in which these transactions are executed, the result should be consistent and predictable.

Outcome:

• Both transactions occur concurrently without interfering with each other.
• Regardless of the execution order, both transactions achieve consistent results.
• Ultimately, both transactions complete successfully, resulting in final balances of $200 for Account A and $1000 for Account B.

Repeatable Reads

In TiDB, Repeatable Read is the default isolation level, meaning that unless explicitly specified otherwise, all transactions in TiDB will operate under the Repeatable Read isolation level. This isolation level guarantees that data read within a transaction remains consistent throughout the transaction. It prevents non-repeatable reads but allows for phantom reads.

Repeatable Read is suitable for applications requiring consistent reads within a transaction, sacrificing some consistency guarantees for improved performance.

In the Repeatable Reads isolation level:

• Transaction 1 reads the balance of Account A, performs the transfer, and commits the transaction.
• Transaction 2 reads the balance of Account A before Transaction 1 commits.
• If Transaction 2 reads Account A again, it should see the same balance it saw initially, regardless of changes made by Transaction 1.

Outcome:

• Transaction 2 consistently reads the initial balance of Account A throughout its execution, ensuring repeatable reads.
• Final balances remain the same as in the Serializable isolation level.

Read Committed

In the Read Committed isolation level, data read by a transaction is committed at the time of reading. This means that transactions can see changes made by other transactions before they are committed, leading to non-repeatable reads and phantom reads. Read Uncommitted is commonly used when immediate data access is required, sacrificing some consistency for improved concurrency.

In the Read Committed isolation level:

• Transaction 1 reads the balance of Account A, performs the transfer, and commits the transaction.
• Transaction 2 reads the balance of Account A after Transaction 1 commits.
• Changes made by Transaction 1 become visible to Transaction 2 only after Transaction 1 commits.

Outcome:

• Transaction 2 may see the updated balance of Account A after Transaction 1 commits, potentially resulting in a non-repeatable read.
• Final balances may vary depending on when Transaction 2 reads the balance of Account A.

Read Uncommitted

The Read Uncommitted isolation level in TiDB allows transactions to read uncommitted data from other transactions. This means transactions can see changes made by other transactions before they are committed, leading to dirty reads, non-repeatable reads, and phantom reads.

Read Uncommitted is the lowest isolation level in TiDB, but it is rarely used due to its minimal data consistency guarantees and potential for data anomalies.

In the Read Uncommitted isolation level:

• Transaction 1 reads the balance of Account A and starts the transfer.
• Transaction 2 reads the balance of Account A before Transaction 1 commits.
• Transaction 2 may see the uncommitted changes made by Transaction 1, including a partial transfer.

Outcome:

• Transaction 2 may observe the balance of Account A changing while Transaction 1 is still in progress, resulting in dirty reads.
• Final balances may vary, and Transaction 2 might see inconsistent data during its execution.

In TiDB, the isolation level can be controlled and set at both the session and global levels using SQL statements or configuration parameters. Let's discuss how this can be achieved:

Controlling Isolation Level at Session Level

To set the isolation level at the session level, you can use SQL statements within the session itself. The changes made will affect only the current session and will not impact other sessions or the global configuration.

SQL Statement to Set Isolation Level at Session Level:

 
SET SESSION TRANSACTION ISOLATION LEVEL “isolation_level”;
	

Replace isolation_level with one of the supported isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ or SERIALIZABLE.

Controlling Isolation Level at Global Level

To set the isolation level at the global level, you need to modify the TiDB configuration file. This will affect the entire TiDB instance and all sessions initiated thereafter unless overridden at the session level.

Configuration Parameter to Set Isolation Level at Global Level

In the TiDB configuration file (usually tidb.toml), you can set the default isolation level using the following parameter:

 
[transaction]

# Set the default transaction isolation level

# Valid values: "READ-UNCOMMITTED", "READ-COMMITTED",

"REPEATABLE-READ", "SERIALIZABLE"

# Default: "REPEATABLE-READ"

isolation-read = "REPEATABLE-READ"
	

Adjust the value of isolation-read to the desired default isolation level.

Scope of Isolation Level Changes

• Session Level: Changes made at the session level are temporary and apply only to the current session. They do not persist beyond the current session's lifetime.
• Global Level: Changes made at the global level are permanent and affect all sessions initiated after the modification. These changes persist even after the TiDB service restarts unless explicitly modified again.

Choosing the Right Isolation Level

Selecting the appropriate isolation level in TiDB depends on the specific requirements of your application. Consider factors such as data integrity, consistency, and performance when deciding which isolation level to use. It's essential to strike a balance between consistency and concurrency to optimize application performance while maintaining data integrity.

Factors to Consider When Choosing Isolation Levels

• Consistency vs. Performance: Each isolation level balances data consistency with system performance and concurrency.
• Transaction Behavior: Understanding how transactions interact and how changes propagate across transactions is crucial for designing robust database systems.
• Application Requirements: Choosing the appropriate isolation level depends on the specific requirements of the application, considering factors such as data integrity, concurrency, and performance.

Ready to optimize your TiDB deployment? Reach out to us for tailored TiDB Consulting and Remote DBA Services. Let's ensure your database meets your specific needs while maintaining data integrity and performance.

{{cta}}