mysql 回滚失败_如何在mysql中回滚失败的查询？

I am trying to catch the error occurred while inserting 2 records in 2 different tables. I want to rollback the already stored queries if the second query failed.

What I have tried:

I have searched for the solution , and i decided to use an if codition inside a procedure to check the number of errors using@@error_count, here is my code:

DELIMITER $$

-- This procedure is used to enter the sql commands

DROP PROCEDURE IF EXISTS `procedure` $$

CREATE PROCEDURE `procedure`()

BEGIN

INSERT INTO `mschema`.`table1`

(`maxbudget`,

`blocked`,

`d_percentage`,

`max discount`)

VALUES

('2250',

'0',

'.9',

'.99');

set @x = @@error_count;

if @x= 0 then

INSERT INTO `mschema`.`table2`

(`name`,`image`,`date`,`fKey_id`)

values

('jhon','jfdd', '2018-01-01 00:00:00', LAST_INSERT_ID());

if @x= 0 then

commit;

else

rollback;

end if;

else rollback;

end if;

END $$

Note that firstdate value is not repetitive in table 2.

when i execute this, it executed successfully and gives me the result in MySQL workbench as follow:

1. -- This procedure is used to enter the sql commands DROP PROCEDURE IF EXISTS `procedure`

2. The second result gave me the second insertion query into table 2 i am trying to execute.

all reult has 0 row(s) affected.

It is also important to say that the inserted values in the 2 tables are not repeated. So i do not know what is wrong.

解决方案-- This procedure is used to enter the sql commands

DROP PROCEDURE IF EXISTS `procedure`

CREATE PROCEDURE `procedure`()

BEGIN

INSERT INTO `mschema`.`table1`

(`maxbudget`,

`blocked`,

`d_percentage`,

`max discount`)

VALUES

('2250',

'0',

'.9',

'.99');

set @x = @@error_count;

if @x= 0 then

INSERT INTO `mschema`.`table2`

(`name`,`image`,`date`,`fKey_id`)

values

('jhon','jfdd', '2018-01-01 00:00:00', LAST_INSERT_ID());

if @x= 0 then

commit;

else

rollback;

end if;

else rollback;

end if;

END

Note that firstdate value is not repetitive in table 2.

when i execute this, it executed successfully and gives me the result in MySQL workbench as follow:

1. -- This procedure is used to enter the sql commands DROP PROCEDURE IF EXISTS `procedure`

2. The second result gave me the second insertion query into table 2 i am trying to execute.

all reult has 0 row(s) affected.

It is also important to say that the inserted values in the 2 tables are not repeated. So i do not know what is wrong.