I am migrating code from Microsoft SQL to Maria DB. In the MSSQL stored procedure a transaction will be started with repeatable read and then later conditionally elevated to serializable if needed. In Maria DB this does not seem possible.
When trying to set the isolation level after the transaction has already been started, I get the error "Transaction characteristics can't be changed while a transaction is in progress."
Is there a way to do this in Maria DB without making the entire transaction serializable?
Here is an example of the Microsoft SQL code I am trying to migrate.
CREATE PROCEDURE -- ...
-- check if there is a transaction already in progress
-- if not, start a new one, otherwise elevate isolation to serializable
DECLARE @TxNested INT = @@TRANCOUNT;
IF @TxNested = 0 BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- lots of code
-- commit transaction if we are the one that started it
IF @TxNested = 0 COMMIT TRANSACTION
END TRY BEGIN CATCH
-- rollback transaction if we are the one that started it
IF @TxNested = 0 ROLLBACK TRANSACTION
It is not possible to elevate the isolation level on a transaction that is already started and
LOCK TABLES does not work inside of stored procedure.
Is there a way to take range locks even if the current isolation is not serializable?