
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- What will happen to the current MySQL transaction if a START TRANSACTION command is executed in the middle of that current transaction?
- What happens to the current MySQL transaction if the session is ended in the middle of a transaction?
- How can we perform COMMIT transactions inside MySQL stored procedure?
- Commit changes on SAP BAPI Transaction
- How can a user start new MySQL transaction?
- How can we perform START transactions inside MySQL stored procedure?
- Which statement, other than START TRANSACTION, is used for starting a transaction?
- How can we implement a MySQL transaction?
- How to start a transaction in JDBC?
- What happens to the current MySQL transaction if the session is killed by DBA?
- What is transaction processing? Explain the properties of the transaction(DBMS)
- What is stored procedure and how can we create MySQL stored procedures?
- Differentiate between cash transaction and credit transaction
- How can we check the current MySQL transaction isolation level?
- How can we find out the current transaction mode in MySQL?

Advertisements