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?
EDIT:
Here is an example of the Microsoft SQL code I am trying to migrate.
CREATE PROCEDURE -- ...
-- ...
AS BEGIN
-- 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;
BEGIN TRY
-- 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
-- ..
END CATCH
END
EDIT 2:
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?
Best Answer
In this case we can acquire a range lock using
SELECT FOR UPDATE
even while in repeatable read isolation. The documentation is not very clear that it will provide a lock for the entire range or just the selected rows.I tested it by first starting a transaction with a
SELECT FOR UPDATE
I purposely left the transaction hang. Then using a second connection attempted to
INSERT INTO
the locked table.The insert would not complete until the transaction was committed.