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?