MariaDB writing transaction and rollback - novice
1
vote
1
answer
2808
views
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.
Asked by JRB
(13 rep)
Jan 13, 2023, 12:02 PM
Last activity: Jan 13, 2023, 03:23 PM
Last activity: Jan 13, 2023, 03:23 PM