How do I use a transaction with a SCHEDULE statement?

Question

I have 2 query in 1 scheduler.

Sample:

CREATE EVENT worker
ON SCHEDULE EVERY 1 DAY STARTS '2017-12-22 00:00:00'
DO

INSERT INTO tbl1 (column) values ('foo')
UPDATE tbl2 SET column = 'foo'

I want to use transaction with this scheduler in MYSQL.

So, for example, if the error in the update query comes up, I want to rollback the insert query. How can I do that ?

Thx for reactions.

Answer 1

Put your statements between START TRANSACTION; and COMMIT;. If any of the statements within the transaction will fail with an error, the COMMIT command will not be executed. Thus the changes will not be persisted and you don't need to rollback explicitly.

You can test it with the following script:

DROP TABLE IF EXISTS `tbl1`;
CREATE TABLE IF NOT EXISTS `tbl1` (
  `column` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `tbl2`;
CREATE TABLE IF NOT EXISTS `tbl2` (
    `column` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
    `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE INDEX `column` (`column`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `worker_log`;
CREATE TABLE `worker_log` (
    `ts` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

DROP EVENT IF EXISTS `worker`;
DELIMITER //
CREATE DEFINER=`root`@`localhost` EVENT `worker` ON SCHEDULE EVERY 10 SECOND
STARTS NOW() + INTERVAL 10 SECOND
ENDS   NOW() + INTERVAL 70 SECOND
ON COMPLETION PRESERVE ENABLE DO BEGIN
    INSERT INTO worker_log (`ts`) values (NOW());
    START TRANSACTION;
        INSERT INTO tbl1 (`column`) values ('foo');
        INSERT INTO tbl2 (`column`) values ('foo');
    COMMIT;
END//
DELIMITER ;

SET GLOBAL event_scheduler = ON;

Now wait a minute and look what has been written to your tables:

select * from worker_log;

ts
-------------------
2017-12-23 16:24:44
2017-12-23 16:24:54
2017-12-23 16:25:04
2017-12-23 16:25:14
2017-12-23 16:25:24
2017-12-23 16:25:34
2017-12-23 16:25:44

You see that the event has been executed every 10 seconds (as defined).

select * from tbl1;

column | ts
-------+-----------------------
foo    | 2017-12-23 16:24:44

select * from tbl2;

column | ts
-------+--------------------
foo    | 2017-12-23 16:24:44

But here you see that the statements within the transaction had only an effect on the first run. This is because the column on tbl2 is defined as UNIQUE, and the second statement failed after the first run, because it tried to insert the same value into this column. But though the first statement should run without problems (since tbl1.column is not UNIQUE), it has also not been persisted.