Example and description of Transaction Management in mysql Stored Procedure

Examples of Transaction Management in mysql stored procedures and descriptions today I have studied how to use transactions in mysql stored procedures. Now I have written a small example to record it here for future reference: www.2cto.com 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, when t_error is set to 1, the subsequent operation */declare continue handler for sqlexception set t_error = 1; -- error processing/** is enabled explicitly, the transaction will temporarily stop automatic commit */-- start transaction;/** disable automatic commit of the transaction */set autocommit = 0; inser T into t_bom_test (parent_id, child_id) values ('D', 'abc'); insert into t_trigger_test (name, age) values ('zhangsan', 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, the following problem occurs: 1) when set autocommit = 0 is started directly; after the final commit or rollback, an Error occurs: Error Code: 10. 48. 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 the SQL code/** mark whether an error occurs */declare t_error int default 0;/** if an SQL exception occurs, then, set t_error to 1 and continue the subsequent operation */declare continue handler for sqlexception set t_error = 1; -- the error handling code exists, if-else is not used at the end. When the rollback statement is directly executed, it is found that even if both insert statements are successfully executed, the statements will be rolled back at the end; when you directly execute the commit statement, you will find that if the first insert statement is successfully executed and the second insert statement fails to be executed, you will find that the transaction has not been 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.