Sample Header Ad - 728x90

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