What happens when we use COMMIT in a MySQL stored procedure and one of the transactions under the START transaction fails?

Suppose one of the queries fails or generates an error, and the other query executes correctly, MySQL still commits the changes for the query that executed correctly. It can be understood from the following example where we are using table "employee.tbl" with the following data -

Example

mysql> Select * from employee.tbl;
+----+---------+
| Id | Name    |
+----+---------+
| 1  | Mohan   |
| 2  | Gaurav  |
| 3  | Sohan   |
| 4  | Saurabh |
| 5  | Yash    |
+----+---------+
5 rows in set (0.00 sec)

mysql> Delimiter //

mysql> Create Procedure st_transaction_commit_save()
    -> BEGIN
    -> START TRANSACTION;
    -> INSERT INTO employee.tbl (name) values ('Rahul');
    -> UPDATE employee.tbl set name = 'Gurdas' WHERE id = 10;
    -> COMMIT;
    -> END //
Query OK, 0 rows affected (0.00 sec)

Now when we call this procedure we know that the UPDATE query will produce an error , because there is no id =10 on our table. But since the first query will execute successfully, COMMIT will save the changes to the table.

mysql> Delimiter ;
mysql> Call st_transaction_commit_save()//
Query OK, 0 rows affected (0.07 sec)

mysql> Select * from employee.tbl;
+----+---------+
| Id | Name    |
+----+---------+
| 1  | Mohan   |
| 2  | Gaurav  |
| 3  | Sohan   |
| 4  | Saurabh |
| 5  | Yash    |
| 6  | Rahul   |
+----+---------+
6 rows in set (0.00 sec)

The above is the detailed content of What happens when we use COMMIT in a MySQL stored procedure and one of the transactions under the START transaction fails?. For more information, please follow other related articles on the PHP Chinese website!