0

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.

3
  • Where you have found TRY-CATCH syntax in MariaDB? I cannot find any words about it in the documentation..
    – Akina
    Commented Jan 13, 2023 at 12:21
  • I found it as examples on various forums. You are right I did not check it against the MariaDB docu. Is there some kind of equivalent in MariaDB for what I am trying to achieve?
    – JRB
    Commented Jan 13, 2023 at 12:31
  • @JeanRandhahn - SQL syntax varies widely. For MariaDB, trust only MariaDB examples, Trust, but with caution, MySQL examples, since they are usually keep up with either, but with a lag.
    – Rick James
    Commented Jan 13, 2023 at 16:05

1 Answer 1

0

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

6
  • Does it have to be in a stored procedure? I need to pass lots of data to insert and as far as I know I cannot pass much and as array to a procedure!? For what is the CHECK constraint good?
    – JRB
    Commented Jan 13, 2023 at 14:03
  • Compound statement needed for handler declaration. But you may, of course, execute the transaction statements interactively and check for each statement execution success visually.
    – Akina
    Commented Jan 13, 2023 at 14:11
  • as far as I know I cannot pass much and as array to a procedure Your knowledge is not correct. You may provide serialized data array as solid SP parameter then parse it in SP and insert row-by-row in a cycle.
    – Akina
    Commented Jan 13, 2023 at 14:12
  • Note that the "3" was rolled back because of the "333".
    – Rick James
    Commented Jan 13, 2023 at 16:07
  • If you try to pass a large array in, it is probably better to populate a table, then you can use the power of SQL, not the clumsiness of SQL's string manipulation functions.
    – Rick James
    Commented Jan 13, 2023 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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