0

I am trying to call a MySQL procedure which is throwing the error

"transaction characteristics can't be changed while a transaction is in progress"

I am making the call to this procedure from a python application, which executes it inside a transaction. The procedure has a structure like below.

    DELIMITER //
    CREATE PROCEDURE sp_test_error()
    BEGIN
       SET TRANSACTION ISOLATION LEVEL READ committed;
       /* some more code*/
    END //
    DELIMITER ;

I understand the error is coming because the procedure is changing the isolation level inside a transaction. After removing the set transaction isolation level statement it is working fine. But after this some times, the table used in the procedure get locked. Maybe it is because of bad architecture or bad code. But as the DB is maintained by some third party, changing that code will not be possible for me. However, I can suggest them some alternate or can change the way I am calling the procedure. What should be the best practice to avoid this?

  • 1
    Move the SET to just before the start of the transaction? – Rick James Jul 24 at 17:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.