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

Suppose one of the queries fails or generates errors and another query (s) properly executed the MySQL still commit the changes of the properly executed query(s). It can be understood from the following example in which we are using the table ‘employee.tbl’ having 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 invoke this procedure, we know that the UPDATE query will produce an error because we do not have id =10 on our table. But as the first query will execute successfully hence the COMMIT will save changes into 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)

Daniol Thomas

Updated on 22-Jun-2020 06:57:24

• Related Questions & Answers
• What happens to the current MySQL transaction if the session is ended in the middle of a transaction?
• What will happen to the current MySQL transaction if a START TRANSACTION command is executed in the middle of that current transaction?
• Commit changes on SAP BAPI Transaction
• How can we perform COMMIT transactions inside MySQL stored procedure?
• How can a user start new MySQL transaction?
• What is transaction processing? Explain the properties of the transaction(DBMS)
• How can we perform START transactions inside MySQL stored procedure?
• Which statement, other than START TRANSACTION, is used for starting a transaction?
• Differentiate between cash transaction and credit transaction
• What happens to the current MySQL transaction if the session is killed by DBA?
• How can we implement a MySQL transaction?
• How to start a transaction in JDBC?
• What is stored procedure and how can we create MySQL stored procedures?
• How can we check the current MySQL transaction isolation level?
• What will happen to MySQL current transaction, if in the middle of that transaction, the DDL statement is executed?