Example and description of transaction management in mysql stored procedure _ MySQL

Example and description of transaction management in mysql stored procedure

Example and description of transaction management in mysql stored procedure

Today I have studied how to use transactions in the mysql stored procedure. now I have written a small example to record it here for reference in the future:

SQL code

Delimiter $

Use test $

Create procedure t_insert_table ()

Begin

/** Mark whether an error occurs */

Declare t_error int default 0;

/** If an SQL exception occurs, set t_error to 1 and continue the subsequent operation */

Declare continue handler for sqlexception set t_error = 1; -- error handling

/** Enable the transaction explicitly. after it is enabled, the transaction will be stopped and automatically committed temporarily */

-- Start transaction;

/** Disable automatic transaction commit */

Set autocommit = 0;

Insert into t_bom_test (parent_id, child_id) values ('D', 'ABC ');

Insert into t_trigger_test (name, age) values ('hangsan', null );

/** The flag is changed, indicating that the transaction should be rolled back */

If t_error = 1 then

Rollback; -- transaction rollback

Else

Commit; -- transaction commit

End if;

-- Rollback;

-- Commit;

End $

Delimiter;

After writing this, I found that many places in the book are directly starting set autocommit = 0; at the end of the commit or rollback, I did it myself, and the results showed the following problems:

1) when set autocommit = 0 is started directly, an error occurs after the final commit or rollback:

Error Code: 1048. Column 'age' cannot be null. then, check the database table and find that the first insert statement is successfully executed and commit is successful, but the second statement is not;

2) when

SQL code

/** Mark whether an error occurs */

Declare t_error int default 0;

/** If an SQL exception occurs, set t_error to 1 and continue the subsequent operation */

Declare continue handler for sqlexception set t_error = 1; -- error handling

These two statements exist, but are not judged by if-else at the end. when the rollback statement is executed directly, it is found that even if both insert statements are successfully executed, but it will still be rolled back at the end. when the commit statement is executed directly, it is found that if the first insert statement is successfully executed and the second insert statement fails to be executed, view the database table, the transaction is not rolled back.

The above two points are my findings and doubts. I hope to record them here and learn from them. of course, I would be very grateful if some friends have helped me solve this confusion.

BitsCN.com