Introduction to MySQL COMMIT
The COMMIT keyword is a MySQL command used for controlling and managing the transaction behavior. A Transaction is defined as a successive group of operations for manipulation of a database that appears to be a single unit. It is combined logically with more than one atomic unit of operations that either succeeds or fails.
Meanwhile, to make a transaction execute successfully we need to assure that each individual operation is completed and committed. If sometimes it so happens that anyone logical unit of operation is failed then, we need to know that the entire transaction is also failed or rolled back.
Let us discuss some of the properties of Transaction in MySQL which is commonly called as ACID properties:
- Atomicity: This property ensures that the SQL queries or operations forming a single unit are committed successfully. But if it fails to show any error then, the transaction is terminated at the point of failure and the earlier operations are forced back to their original state by the rollback.
- Consistency: It determines that any changes made in the database through a successful transaction will change the states 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, 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.
For changing the auto-commit mode you can either enable or disable the current transaction state 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 AUTO COMMIT command as follows:
SET AUTOCOMMIT = 0;
SET AUTOCOMMIT = OFF;
And the following to explicitly make commit auto:
SET AUTOCOMMIT = 1;
SET AUTOCOMMIT = ON;
How to COMMIT works in MySQL?
Generally, a transaction starts with BEGIN WORK SQL statement and ends up by a COMMIT or, ROLLBACK SQL statement. Between these beginning and end terms, the SQL commands are entered which performs the transaction.
In MySQL Transaction, COMMIT and ROLLBACK keywords are used mainly to state 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. Whereas, ROLLBACK command allows to return the table involved in the transaction 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 then, it accepts each SQL statement in the transaction or not to be complete and is committed at the end.
But when 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 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
2. Get the latest customer number
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.
Here, we have started our transaction and next chosen the latest Customer number form the orders table and applied for the next number as the new customer number and inserted it into the table. Also, inserted customer numbers in customers table and then finally using COMMIT statement committed the transaction.
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;
If we individual view both tables, we can see the following changes in the tables:
- To use the Transaction process in MYSQL, the tables should be formed in a special way. The most popular table that supports transactional operations is InnoDB.
- We need to have a MySQL version that should have a particular compilation feature.
- Basically, when a Transaction is committed using COMMIT, then the modifications are saved permanently. You can use the ROLLBACK command which cancels all the transaction changes and rolls back the state before a COMMIT is executed in the table.
- But however, in MySQL, there is no ROLLBACK because here the AUTOCIMMITT mode is enabled. Therefore, in MySQL, nearly all statements cannot be reverted back.
- 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 can skip the problem.
Logically, while writing an SQL statement for COMMIT to perform a transaction, then we will combine many SQL queries to a group and execute them all together as a single transaction.
When any transaction is successfully completed in the table suppose for inserting an order or inventory record then, the COMMIT command is used to alert the changes that took place in the tables involved during the transaction.
This is a guide to MySQL COMMIT. Here we discuss the Examples to Implement COMMIT in MySQL along with customer table, order table, and script codes. You may also have a look at the following articles to learn more –