Elevate transaction isolation in Maria DB

Question

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?

Answer 1

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

START TRANSACTION;
SELECT * FROM `Foo` FOR UPDATE;

I purposely left the transaction hang. Then using a second connection attempted to INSERT INTO the locked table.

INSERT INTO `Foo` (`Bar`) VALUES ('Bar');

The insert would not complete until the transaction was committed.

Answer 2

You have to specify GLOBAL or SESSION (I suspect SESSION in your case):

START TRANSACTION;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
COMMIT;

See also the MariaDB documentation on isolation levels.

It's not possible to change the isolation level of the transaction you're in already. SET SESSION TRANSACTION ... will only take effect from the next transaction in the session onwards.