MysqlHow-ToBeginner · 3 min read

How to Set Transaction Isolation Level in MySQL

In MySQL, you set the transaction isolation level using the SET TRANSACTION ISOLATION LEVEL statement for the current transaction or SET SESSION TRANSACTION ISOLATION LEVEL to set it for the session. You can also set it globally with SET GLOBAL TRANSACTION ISOLATION LEVEL, but this requires restart or new connections to take effect.

📐

Syntax

The transaction isolation level controls how transactions interact with each other. You can set it for the current transaction, the current session, or globally.

SET TRANSACTION ISOLATION LEVEL: Sets isolation level for the next transaction only.
SET SESSION TRANSACTION ISOLATION LEVEL: Sets isolation level for all transactions in the current session.
SET GLOBAL TRANSACTION ISOLATION LEVEL: Sets default isolation level for all new connections.

Common isolation levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

sql

SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

SET SESSION TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

SET GLOBAL TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

💻

Example

This example shows how to set the isolation level to READ COMMITTED for the current session and then start a transaction.

sql

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- Your transactional queries here
COMMIT;

Output

Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)

⚠️

Common Pitfalls

Common mistakes include:

Setting the isolation level globally but expecting it to affect current sessions immediately.
Not setting the isolation level before starting a transaction, so the default level applies.
Confusing session and transaction scope, leading to unexpected behavior.

Always set the isolation level before START TRANSACTION if you want it to apply to that transaction.

sql

/* Wrong: Setting isolation level after starting transaction */
START TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- This will not affect the current transaction
COMMIT;

/* Right: Set before starting transaction */
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
-- This transaction uses SERIALIZABLE isolation
COMMIT;

📊

Quick Reference

Command	Scope	Description
SET TRANSACTION ISOLATION LEVEL ...	Next transaction	Sets isolation level for the next transaction only
SET SESSION TRANSACTION ISOLATION LEVEL ...	Current session	Sets isolation level for all transactions in current session
SET GLOBAL TRANSACTION ISOLATION LEVEL ...	Global	Sets default isolation level for new connections

✅

Key Takeaways

Set the transaction isolation level before starting a transaction to ensure it applies.

Use SET SESSION to change isolation level for all transactions in your current connection.

SET GLOBAL changes affect new connections, not existing ones.

Common isolation levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

Choose the isolation level based on your application's consistency and concurrency needs.