If you have been dealing with databases, it must feel like you are handling a bunch of pieces. You need to somehow make sure that they fit together perfectly, and that is where transaction management comes into the picture. And for that, the most important data handling operations are AUTOCOMMIT, COMMIT, and ROLLBACK. In this tutorial, we are going to study these in detail with the help of practical examples. Let’s go for it!
Understanding Transaction
Let’s take a scenario, where you’re at a checkout counter and you’re buying groceries. You put all the items on the conveyor belt and the cashier scans them one by one. Then, if you anyway change your mind halfway through and decide you don’t want certain items, you can ask the cashier to put them back. In database parlance, this whole process is like a transaction, everything goes (COMMIT) or nothing goes (ROLLBACK).
AUTOCOMMIT: Default Behavior
If the AUTOCOMMIT mode is enabled (which is enabled by default) each individual statement forms a single transaction on its own, and it is automatically committed immediately after you execute the query. This means that, if you have made any changes to the SQL statement, it becomes permanent in the database.
It is like the grocery store hired a super efficient cashier who rings up each item as soon as it is scanned.
Syntax:
To turn off AUTOCOMMIT:
SET AUTOCOMMIT = OFF;Code language: SQL (Structured Query Language) (sql)With this setting set to off, our transaction will not save automatically. We would need to manually save each transaction.
To save this, execute the below query:
COMMIT;Code language: SQL (Structured Query Language) (sql)We will understand the concept of COMMIT later in this tutorial.
To turn on the AUTOCOMMIT back on:
SET AUTOCOMMIT = ON;Code language: SQL (Structured Query Language) (sql)Example:
Imagine a scenario where you are an employee in a finance application-based company and, your job is to transfer funds between two accounts. For the security check, you need to ensure that the withdrawals from one account have to be deposited in another account. Either both happens or neither happens. Below is the table named accounts, which contains the account details.
Starting a Transaction: First, you need to turn off the AUTOCOMMIT mode to go ahead with a manual transaction. This will ensure that the changes are not automatically saved to the database.
SET AUTOCOMMIT = OFF;Code language: SQL (Structured Query Language) (sql)Executing Multiple Statements: Now, you need to withdraw money from one account and deposit it into another account.
Withdraw from Account 1:
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;Code language: SQL (Structured Query Language) (sql)The above query will decrease the balance of account 1 by 100 units.
Deposit into Account 2:
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;Code language: SQL (Structured Query Language) (sql)The above query will increase the balance of account 2 by 100 units.
Both of the queries are part of the same transaction and if something goes wrong during this process of these queries, you do not want any changes to be saved.
COMMIT: Making Permanent Changes
The COMMIT statement in MySQL allows you to permanently save changes made to the current transaction. All changes made to the statement are saved to the database and are permanent. When a COMMIT is executed the story ends, and a new story begins. A new transaction starts after the COMMIT statement is executed.
Syntax:
To COMMIT the current transaction:
COMMIT;Code language: SQL (Structured Query Language) (sql)Example:
Let’s continue our previous scenario of the transaction, now when you see that withdrawal and deposits are successful, you can commit the transaction to make the changes permanent.
COMMIT;Code language: SQL (Structured Query Language) (sql)When you execute the above query, it will ensure that the transactions between both accounts are permanently recorded in the database.
ROLLBACK: Undoing Changes
The ROLLBACK statement in MySQL allows you to undo the changes that have been made in the current transaction. When a ROLLBACK statement is executed, it reverts the database back to the state in which the transaction was started.
Syntax:
ROLLBACK;Code language: SQL (Structured Query Language) (sql)Example:
Imagine the previous transaction scenario, and let’s say that something goes wrong during the transaction process. Don’t worry, you can undo all the changes that are made so far by using:
ROLLBACK;Code language: SQL (Structured Query Language) (sql)
The above query will take back the database, where it was before the transaction began. This will ensure that if the withdrawal from account 1 was successful but the deposit into account 2 failed, both the changes are then undone, and the balances remain as they were before the transaction started.
Note: You cannot “ROLLBACK” once you “COMMIT” the statement.
Conclusion
While we write queries, there are chances of mistakes or errors and as a developer, we may want to go back to our previous data. That’s where AUTOCOMMIT, COMMIT, and ROLLBACK come into the picture. Using these three effectively for transaction-related problems can help in maintaining a consistent database, which is important for application development.
Also Read:
- How to Extract the Year from a Datetime Column in MySQL
- Creating Pivot Tables in PostgreSQL Using the crosstab() Function