I have the following two procedures on my server:
CREATE DEFINER=`someone`@`localhost`
PROCEDURE `NewMagnet`(
IN `nick` VARCHAR(32),
IN `tth` CHAR(39),
IN `name` TINYTEXT,
IN `size` BIGINT,
IN `eid` INT,
OUT `maid` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Automated insertion to magnets and filename tables'
BEGIN
INSERT INTO magnets (eid, tth, size, nick, date)
SELECT
eid,
tth,
size,
m.id,
NOW()
FROM modtable m
WHERE m.nick = nick;
SET maid = LAST_INSERT_ID();
INSERT INTO filenames
VALUES( LAST_INSERT_ID(), name );
END
This procedure is called from inside of another procedure named NewEntry:
CREATE DEFINER=`someone`@`localhost`
PROCEDURE `NewEntry`(
IN `ctg` VARCHAR(15),
IN `msg` TINYTEXT,
IN `nick` VARCHAR(32),
IN `tth` CHAR(39),
IN `name` TINYTEXT,
IN `size` BIGINT,
OUT `eid` INT,
OUT `maid` INT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Automated procedure to insert a new entry'
BEGIN
INSERT INTO entries (msg, nick, date, ctg)
SELECT
msg,
m.id,
NOW(),
c.id
FROM modtable m, ctgtable c
WHERE c.name = ctg
AND m.nick = nick
LIMIT 1;
SET eid = LAST_INSERT_ID();
CALL NewMagnet( nick, tth, name, size, eid, maid );
END
I want the NewEntry procedure to rollback itself if the call to NewMagnet fails. Currently, what happens is that a new entry is added to the entries table even if the magnets table INSERT query failed for any reason (duplicate key, mostly).
I've read this thread on Stack Overflow ( https://stackoverflow.com/a/20046066/1190388 ) for creating a handler in a procedure, but I am unaware about how to implement it for the parent procedure NewEntry in my case.
EDIT
Based on the thread linked above; I updated my procedures to the following:
NewEntry
BEGIN
DECLARE exit handler for SQLEXCEPTION
BEGIN
ROLLBACK;
END;
DECLARE exit handler for SQLWARNING
BEGIN
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO entries (msg, nick, date, ctg)
SELECT
msg,
m.id,
NOW(),
c.id
FROM modtable m, ctgtable c
WHERE c.name = ctg
AND m.nick = nick
LIMIT 1;
SET eid = LAST_INSERT_ID();
CALL NewMagnet( nick, tth, name, size, eid, maid );
COMMIT;
END
NewMagnet
BEGIN
DECLARE exit handler for sqlexception
BEGIN
ROLLBACK;
END;
DECLARE exit handler for sqlwarning
BEGIN
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO magnets (eid, tth, size, nick, date)
SELECT
eid,
tth,
size,
m.id,
NOW()
FROM modtable m
WHERE m.nick = nick;
SET maid = LAST_INSERT_ID();
INSERT INTO filenames
VALUES( LAST_INSERT_ID(), name );
COMMIT;
END
But it is still inserting a new row to the entries table even when the duplicate key exists for the magnets values passed.
EDIT
To explain what is happening and to clear some doubts Anup has; I created two new tables:
CREATE TABLE `a` (
`b` TINYINT(3) UNSIGNED NOT NULL,
PRIMARY KEY (`b`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;
CREATE TABLE `b` (
`id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
`f` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;
And then created two new procedures as well:
CREATE PROCEDURE `s`(IN `f` TINYINT, OUT `z` TINYINT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'dsf'
BEGIN
DECLARE exit HANDLER
FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET z = -1;
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO a
VALUES( f );
SET z = f;
COMMIT;
END
and
CREATE PROCEDURE `t`(IN `r` TINYINT, OUT `l` TINYINT, OUT `z` TINYINT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE exit HANDLER
FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET l = -1;
SET z = -1;
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO b (f)
VALUES( r );
SET l = LAST_INSERT_ID();
CALL s( r, z );
COMMIT;
END
Then, I executed following commands:
CALL s(3, @z);
SELECT @z;
/* Affected rows: 0 Found rows: 1 Warnings: 0 Duration for 2 queries: 0.046 sec. */
This inserted the value 3 in my table a. z was returned as 3 correctly. Next, I executed:
CALL s(3, @z);
SELECT @z;
/* Affected rows: 0 Found rows: 1 Warnings: 1 Duration for 2 queries: 0.000 sec. */
And the warning led to the rollback and I received z as -1. So far so good.
Now, I executed following statements:
CALL t(3, @l, @z);
SELECT @l, @z;
/* Affected rows: 0 Found rows: 1 Warnings: 1 Duration for 2 queries: 0.015 sec. */
CALL t(3, @l, @z);
SELECT @l, @z;
/* Affected rows: 0 Found rows: 1 Warnings: 1 Duration for 2 queries: 0.016 sec. */
CALL t(3, @l, @z);
SELECT @l, @z;
/* Affected rows: 0 Found rows: 1 Warnings: 1 Duration for 2 queries: 0.000 sec. */
And the value for z was returned always as -1 but the value of l was incremented every time. So, now I have the table b like in the picture below:
Which is exactly what I want to avoid.
Best Answer
EDIT:1
here is the demo SQL FIDDLE
inner procedure also has transaction and that is doing all previous DML COMMIT. If you move your Insert after the CALL in outer procedure you will there is no insert. but it may not always possible to change order. somehow we have to avoid the COMMIT from any nested procedure call and do only in the parent procedure.
but sometime those nested procedures can be called independently and you may want to keep Transactions for those calls. so basically what I am doing is adding Transaction/Rollback/Commit statement based on the parameter passed to the procedure.
when making a call directly from APP Code to procedure always pass 1 and hence the procedure will be use explicit Transaction. but when you making nested call in parent procedure you do not want to use transaction for nested calls so explicitly pass 0.