Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
Updated on: 22-Jun-2020
458 Views
Advertisements