📋

Description

This error indicates an attempt to modify the transaction isolation level (e.g., READ COMMITTED, REPEATABLE READ) while an active transaction is already in progress within the current database session. MariaDB requires that transaction isolation levels are established before a transaction begins to maintain data consistency and predictable behavior throughout its duration.

💬

Error Message

Transaction isolation level can't be changed while a transaction is in progress

🔍

Known Causes

3 known causes

⚠️

Explicit Transaction Active

You have explicitly initiated a transaction using `START TRANSACTION` or `BEGIN` which is still active and needs to be completed.

⚠️

Implicit Transaction Started

An operation or client tool may have implicitly started a transaction, making it impossible to alter the isolation level mid-transaction.

⚠️

Autocommit Disabled

If `autocommit` is set to `OFF`, every statement implicitly starts a transaction that remains active until explicitly committed or rolled back.

🛠️

Solutions

3 solutions available

1. Complete the Current Transaction easy

The most direct solution is to finish the ongoing transaction before attempting to change the isolation level.

1

Identify and complete the transaction that is currently in progress. This typically involves issuing a COMMIT or ROLLBACK statement.

COMMIT;
-- or
ROLLBACK;

2

Once the transaction is completed, you can then safely set the desired isolation level.

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

2. Set Isolation Level at Connection Start medium

Configure the isolation level when establishing a new database connection to prevent conflicts.

1

Modify your application's database connection logic to include the `transaction_isolation` parameter. This ensures the isolation level is set immediately upon connection, before any transactions begin.

Example for a Python application using `mysql.connector`:

python
import mysql.connector

config = {
  'user': 'your_user',
  'password': 'your_password',
  'host': 'your_host',
  'database': 'your_database',
  'transaction_isolation': 'REPEATABLE READ' # Set the desired isolation level here
}

try:
    cnx = mysql.connector.connect(**config)
    print("Successfully connected with REPEATABLE READ isolation.")
    # ... perform database operations ...
except mysql.connector.Error as err:
    print(f"Error: {err}")
finally:
    if 'cnx' in locals() and cnx.is_connected():
        cnx.close()

2

For other programming languages or ORMs, consult their specific documentation for setting connection-level transaction isolation parameters. Common values include READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

Consult your ORM or database connector documentation for syntax.

3. Adjust Application Logic to Avoid Concurrent Isolation Changes advanced

Rethink your application's workflow to prevent simultaneous attempts to change isolation levels and start transactions.

1

Analyze your application code to identify where and when `SET SESSION TRANSACTION ISOLATION LEVEL` is called. Ensure these calls are not happening concurrently with operations that start new transactions.

Review your application's database interaction modules.

2

Implement locking mechanisms or sequential execution logic within your application to serialize operations that involve setting the isolation level and starting transactions. For instance, use mutexes or semaphores if your application is multi-threaded.

Example pseudocode for a multi-threaded application:


lock.acquire()
try:
    # Set isolation level
    execute_sql("SET SESSION TRANSACTION ISOLATION LEVEL ...")
    # Start and commit/rollback transaction
    execute_sql("START TRANSACTION")
    # ... operations ...
    execute_sql("COMMIT")
finally:
    lock.release()

3

Consider using stored procedures for complex transactional logic. This can encapsulate the isolation level setting and transaction execution, reducing the likelihood of race conditions from the application side.

Example stored procedure:

sql
DELIMITER //
CREATE PROCEDURE PerformTransactionalWork() 
BEGIN
  SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  START TRANSACTION;
  -- ... your SQL statements ...
  COMMIT;
END //
DELIMITER ;

-- To call:
CALL PerformTransactionalWork();

🔗

Related Errors

5 related errors

MariaDB Error 1568: Isolation Level Change in Progress