Fixed: "ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress" in MySQL

Setting the isolation level failed with ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress. A transaction's isolation level is decided before it starts, so this one's a question of order.

What I Was Doing

I was in a transaction on MySQL 9.7.4 in Docker, and decided the next part needed a stricter isolation level:

mysql

START TRANSACTION;
UPDATE products SET ReorderLevel = 5 WHERE Id = 3;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

What I Expected

Query OK, and the rest of the transaction running at SERIALIZABLE.

What I Got

Output (the last statement)

ERROR 1568 (25001): Transaction characteristics can't be changed while a transaction is in progress

What I Checked

The transaction's real level. From a root session, the open transaction was running at the level it started with:

mysql: root session

SELECT trx_mysql_thread_id, trx_isolation_level FROM information_schema.innodb_trx;

Output

+---------------------+---------------------+
| trx_mysql_thread_id | trx_isolation_level |
+---------------------+---------------------+
|                  72 | READ COMMITTED      |
+---------------------+---------------------+
1 row in set (0.002 sec)

The SET TRANSACTION page. It says the statement "applies only to the next single transaction performed within the session." The next one, not the current one.

Why It Happens

Isolation is a property of the whole transaction. MySQL can't change the rules halfway through, because what the transaction has already read and locked was done under the old ones.

The Fix

End the transaction first, then set the level, then start the next one:

mysql

COMMIT;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT COUNT(*) FROM products;

After the Fix

Output (SET TRANSACTION)

Query OK, 0 rows affected (0.000 sec)

Output (innodb_trx, from the root session)

+---------------------+---------------------+
| trx_mysql_thread_id | trx_isolation_level |
+---------------------+---------------------+
|                  72 | SERIALIZABLE        |
+---------------------+---------------------+
1 row in set (0.001 sec)

Common Mistakes

Mistake	What happens	Do this instead
Setting the level after START TRANSACTION	ERROR 1568	Set it first
Writing SET TRANSACTION and expecting it to last	It applies once	Use SET SESSION TRANSACTION
Committing just to get past the error, halfway through your work	The first half is saved, under the old level	Plan the level before you start

Troubleshooting

Symptom	Likely cause	Fix
The same error with READ ONLY	The same rule for the access mode	Set it first
The error with no START TRANSACTION in sight	autocommit = 0 keeps a transaction open	COMMIT first
The error from an application	It reuses a connection with an open transaction	Check that each path commits or rolls back

Conclusion

ERROR 1568 means a transaction is open. End it, set the level, then start the next one. How to Check and Change the Transaction Isolation Level in MySQL covers the scopes.

Additional Resources

• SET TRANSACTION Statement — when each form applies.

Related

• How to Check and Change the Transaction Isolation Level in MySQL
• READ COMMITTED vs REPEATABLE READ in MySQL
• How to Set the Transaction Isolation Level in EF Core