MariaDB writing transaction and rollback - novice

Question

Currently I am learning how to write transactions for MariaDB (I got a version 10.6 running). I attempt to execute several commands and based on their success to commit or rollback.

So far I have searched google for a few days and got really stuck not knowing how to go about it. This is how far I got and cannot figure out what is wrong. I am very grateful for any pointers!

Cheers
JR

DROP TABLE IF EXISTS `FWtest`.`testtrans`;
CREATE TABLE `FWtest`.`testtrans` ( 
    `ID` INT NOT NULL AUTO_INCREMENT , 
    `ver` INT NULL DEFAULT NULL , 
    `name` VARCHAR(10) NULL DEFAULT NULL , 
    `savedatetime` DATETIME NULL DEFAULT NULL , 
PRIMARY KEY (`ID`)) ENGINE = InnoDB;
DROP TABLE IF EXISTS FWtest.testtrans2;
CREATE TABLE FWtest.testtrans2 ( 
    ID INT NOT NULL , 
    param1 INT NULL DEFAULT NULL , 
    param2 INT NULL DEFAULT NULL)
ENGINE = InnoDB;
DELIMITER |
START TRANSACTION
    BEGIN NOT ATOMIC TRY;
        SET @config_name='test';
        -- get the next version number, set 0 if none
        SELECT @config_version:= IFNULL(max(ver)+1,0) FROM FWtest.testtrans WHERE name = @config_name;
        INSERT INTO FWtest.testtrans(name, ver, savedatetime) 
          VALUES ( @config_name, @config_version, NOW() );
    SELECT @config_id:=LAST_INSERT_ID();

    INSERT INTO `FWtest`.`testtrans2`(`ID`, `param1`, `param2`) 
      VALUES 
        (@config_id, 1, null),
        (@config_id, 2, 'aa');  -- this line will make the insert fail

END TRY
BEGIN CATCH
    ROLLBACK;
END CATCH

COMMIT|
DELIMITER ;

SOLUTION: Thanks to the pointers of Akina I worked out a solution using an exception handler directly in a transaction:

DELIMITER |
BEGIN NOT ATOMIC
    DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
    START TRANSACTION;
            SET @config_name='test';
            SELECT @config_version:= IFNULL(max(`ver`)+1,0) FROM `FWtest`.`testtrans` WHERE `name` = @config_name;
            INSERT INTO `FWtest`.`testtrans`(`name`, `ver`, `savedatetime`) VALUES ( @config_name, @config_version, NOW() );
        SELECT @config_id:=LAST_INSERT_ID();

        INSERT INTO `FWtest`.`testtrans2`
        (`ID`, `param1`, `param2`) 
        VALUES 
          (@config_id, 1, null),
          (@config_id, 2, 'nn');    -- this line will make the insert fail


COMMIT;

END|
DELIMITER ;

Note: When using this sample code in a sql tool (e.g. DBeaver) then error checking and rollback needs to be turned off in order for the transaction to handle the error itself.

score 0 · Accepted Answer · answered Jan 13 '23 at 12:29

Tiny sample:

Create test table with CHECK constraint:

CREATE TABLE test (id INT CHECK (id < 100)) SELECT 1 id UNION SELECT 11;
SELECT * FROM test;

id
1
11

Create stored procedure with the transaction and error handling:

CREATE PROCEDURE test (value1 INT, value2 INT)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK; -- if an error occures then rollback
START TRANSACTION;
  INSERT INTO test VALUES (value1);
  INSERT INTO test VALUES (value2);
COMMIT;
END

Call SP with valid parameters:

CALL test (2, 22); -- valid parameters, inserts will be committed
SELECT * FROM test;

id
1
11
2
22

Call SP with invalid parameters:

CALL test (3, 333); -- invalid parameters, inserts will be rollbacked
SELECT * FROM test;

id
1
11
2
22

fiddle

MariaDB writing transaction and rollback - novice

1 Answers1