1

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?

  • I would question the need for ever changing the Isolation Level. Would you care to share the SQL in the transaction? – Rick James Nov 20 '18 at 16:54
  • We have some stored procedures that only need repeatable read and we have some stored procedures that require serializable. The procedures that are serializable can be conditionally called from the procedures only requiring repeatable read. In this case the procedures requiring serializable need to elevate the current transaction isolation level. Multiple stored procedure calls may be made in a single transaction. – bigjbomb Nov 20 '18 at 17:12
  • In MariaDB (and MySQL) a call to a stored procedure is implicitly a new transaction. – dbdemon Nov 20 '18 at 17:17
  • 2
    I can't find anything in the documentation that a stored procedure starts a new transaction. Also if that is the case what happens to the current transaction? – bigjbomb Nov 20 '18 at 17:31
  • @bigjbomb - Sorry, it turns out I was wrong about sproc calls implicitly starting new transactions! (Which is good!) dba.stackexchange.com/questions/223034/… – dbdemon Nov 20 '18 at 21:57
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.

0

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.

  • I am not sure since the documentation is not very clear on this but I am under the impression that this will only take effect at the start of the next transaction in the session and not the current transaction. – bigjbomb Nov 20 '18 at 17:02
  • According to the documentation page I linked to, both SET TRANSACTION ... and SET SESSION TRANSACTION ... will only take effect starting with the next transaction. SESSION will effect all subsequent transactions in the session, whereas without SESSION it will only affect the next transaction in the current session. – dbdemon Nov 20 '18 at 17:22
  • I want to elevate the current transactions isolation level. – bigjbomb Nov 20 '18 at 17:25

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.