Updated June 2, 2023
Introduction to MySQL COMMIT
MySQL’s COMMIT keyword helps manage transactions and control their behavior. A Transaction is a successive group of operations for manipulating a database that appears to be a single unit. Multiple atomic operations units can succeed or fail when you logically combine them.
Completing and committing each operation is crucial for ensuring the success of a transaction. If any logical unit of operation fails, the entire transaction will either fail or be rolled back.
Let’s discuss some of the commonly known ACID properties, which describe the characteristics of transactions in MySQL:
- Atomicity: This property guarantees the successful commitment of a set of SQL queries or operations that form a single unit. However, if an error occurs, the transaction terminates at the point of failure, and the rollback mechanism reverts the previous operations to their original state.
- Consistency: It determines that any changes made in the database through a successful transaction will change the state of the database.
- Isolation: This property assures that the transactions made are independent and transparent to each other.
- Durability: It provides the feature of transaction persistence of a completed committed transaction result even if a system failure occurs.
Syntax of MySQL COMMIT
In MySQL, we can learn the important statements below to manage transactions:
We need to use the START TRANSACTION command to initiate a transaction. The alias of it can be BEGIN or BEGIN WORK.
Then, the COMMIT command is used to make the effect of the changes in the table due to the current transaction.
Again, use the ROLLBACK statement to re-back the changes done and cancel the committed one.
To change the auto-commit mode, turn the current transaction state on or off and use SET AUTOCOMMIT.
But in MySQL, the COMMIT automatically shows the changes on the table by default. To avoid this in the database, you can set the limits of the AUTO COMMIT command as follows:
SET AUTOCOMMIT = 0;Or,
SET AUTOCOMMIT = OFF;And the following to explicitly make commit auto:
SET AUTOCOMMIT = 1;Or,
SET AUTOCOMMIT = ON;How to COMMIT work in MySQL?
Generally, a transaction starts with BEGIN WORK SQL statement and ends with a COMMIT or ROLLBACK SQL statement. To execute the transaction, you enter the SQL commands between the beginning and end terms. In MySQL Transaction, the COMMIT and ROLLBACK keywords primarily determine the behavior of changes in the table.
COMMIT command helps to show the effect of a successful transaction when completed to all the tables involved. At the same time, the ROLLBACK command allows the return of the transaction’s table to its previous state when a failure is made.
Hence, we can also control the state of a transaction by defining a session variable in MySQL named AUTOCOMMIT. Here, when we set AUTOCOMMIT to 1, which is also a default value, it accepts each SQL statement in the transaction or not to be complete and is committed at the end.
But when the AUTOCOMMIT value is 0 issued by the command: SET AUTOCOMMIT = 0, the succeeding series of SQL statements performs as a transaction, and unless a clear COMMIT Statement is made, no activities in the transaction are committed.
Examples to Implement COMMIT in MySQL
To understand COMMIT more clearly, let us take an example of inserting a row of data values in the sample tables created named ‘Customers’ and ‘Orders.’
Here is the Customers Table:
Here is the Orders Table:
We are demonstrating to explain the COMMIT in a MySQL Transaction by adding portions of SQL queries forming a SQL statement, and finding out when the rows need to be committed or rolled back.
- Following is the script code to perform the transaction:
1. Starting a new MySQL Transaction
START TRANSACTION;2. Get the latest customer number
SELECT
@CustomerNum:=MAX(CustomerNum)+1
FROM Orders;3. Inserting a new row for OrderNum 612
INSERT INTO orders (CustomerNum, OrderNum, Status, Budget)
VALUES(@CustomerNum, '612', 'Success', '5000');4. Insert order line items
INSERT INTO customers(CustomerNum, Name, Payment_purpose, Amount, City) VALUES(@CustomerNum,'abc', 'bill', '2000','Delhi'), (@CustomerNum,'abc2', 'shopping', '2500','Noida');5. Commit changes will be reflected in the table.
Output:
Here, we started our transaction and next chose the latest Customer number from the orders table, applied for the following number as the new customer number, and inserted it into the table. Also, I inserted customer numbers in the customer table and finally committed the transaction using a COMMIT statement.
Optionally, you can view the new row of order items selected from both the tables Orders and Customers.
- To view the newly created rows, we will use the below SQL query:
SELECT a.CustomerNum, Name, Payment_purpose, Amount, City, OrderNum, Status, Budget FROM Orders a INNER JOIN Customers b USING (CustomerNum) WHERE a.CustomerNum = 56;Output:
If we individually view both tables, we can see the following changes in the tables:
Customers Table:
Orders Table:
- To utilize the transaction process in MySQL, you need to structure the tables in a specific manner. The most popular table that supports transactional operations is InnoDB.
- We need to have a MySQL version that should have a particular compilation feature.
- When you commit a transaction using the COMMIT command, it permanently saves the modifications. To cancel all changes made within a transaction and revert the table to its state before executing the COMMIT, you can utilize the ROLLBACK command.
- However, there is no ROLLBACK in MySQL because the AUTOCIMMITT mode is enabled here. Therefore, in MySQL, nearly all statements cannot be reverted.
- We need to have database backup to restore the transaction committed and also can use DBA tools to replay all data modifiers from the logs or skip the problem.
Conclusion
Logically, while writing an SQL statement for COMMIT to perform a transaction, we will combine many SQL queries into a group and execute them together as a single transaction.
Suppose the table completes a transaction, like inserting a record for an order or inventory. The COMMIT command alerts the changes in the tables involved during the transaction.
Recommended Articles
We hope that this EDUCBA information on “MySQL COMMIT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
