Generally how to write transaction

• Begin transaction by issuing the SQL command BEGIN WORK.
• Issue one or more SQL commands like SELECT, INSERT, UPDATE or DELETE.
• Check if there is no error and everything is according to your requirement.
• If there is any error, then issue a ROLLBACK command, otherwise issue a COMMIT command.

how to decide to commit or roll back

method 1

use 13.6.7.2. DECLARE … HANDLER Syntax in the following way:

DELIMITER $$

CREATE PROCEDURE `sp_fail`()
BEGIN
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    START TRANSACTION;
    INSERT INTO `tablea` (`date`) VALUES (NOW());
    INSERT INTO `tableb` (`date`) VALUES (NOW());
    INSERT INTO `tablec` (`date`) VALUES (NOW()); -- FAIL
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END$$

DELIMITER ;

automatically rollback method 2:

   BEGIN

   DECLARE exit handler for sqlexception
      BEGIN
      ROLLBACK;
   END;

   DECLARE exit handler for sqlwarning
     BEGIN
     ROLLBACK;
   END;

   START TRANSACTION;

   INSERT INTO prp_property1 (module_name,environment_name,NAME,VALUE) VALUES ('','production','','300000');

   [ERROR]

   COMMIT;

   END

Isolation level

https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

https://medium.com/@huynhquangthao/mysql-testing-isolation-levels-650a0d0fae75

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Other DBs


MS-SQL will automatically roll-back if failed
https://www.sqlshack.com/rollback-sql-rolling-back-transactions-via-the-rollback-sql-query/