Handling Transactions In MySQL

MySQL provides various database engines to handle the SQL queries. The most popular engines are MyISAM and InnoDB. Out of these two engines, InnoDB supports transactions which means we can commit and rollback to perform an operation involving multiple queries as a single unit. The same is not possible with MyISAM since it does not support transactions. InnoDB is more reliable as compared to MyISAM since it uses transactional logs for auto-recovery.

Notes: MySQL is planning to completely remove MyISAM since InnoDB is much better in performance as compared to MyISAM.

This tutorial provides the details to handle transactions in MySQL using START TRANSACTION, COMMIT, and ROLLBACK statements. Though we can execute the SQL queries separately which is the ideal scenario, in several cases we need to make sure that all the queries specific to a task must either succeed or fail due to failure of either of the query. We can consider such tasks as a single unit involving multiple operations or queries to create, update, or delete rows. Hence, in a transactional unit having multiple operations, it must either succeed or fail.

One must take care while handling transactions since there are certain statements that cannot be rolled back. These includes CREATE/DROP database, CREATE/ALTER/DROP table or stored routines.

Properties of a Transaction

Below listed are the four standard properties of a transaction. These are also called as ACID.

Atomicity- It ensures that all operations involved in a task or unit are completed successfully. In case of failure of either of the operations, the transaction must be aborted and all the previous operations must be rolled back to their former state. It means in case of failure of a transaction, none of the operations involved in it must succeed.

Consistency- Data must be in a consistent state at the start and end of the transaction to ensure that the database changes states to reflect the changes upon a successfully committed transaction.

Isolation- The transaction must be completed in isolation hiding the intermediate states with other transactions. Each transaction must operate independently and transparently to each other.

Durability- It ensures that the changes in data as part of a transaction persists even in case of a system failure. The changes must not be undone even in the case of a system failure.

Transaction Statements

START TRANSACTION- We can use START TRANSACTION or BEGIN or BEGIN WORK to start the transaction. The BEGIN or BEGIN WORK are the aliases of START TRANSACTION.

COMMIT- In case of success, the COMMIT command must be issued at the end of the transaction to persist the changes.

ROLLBACK- In case of any failure, the ROLLBACK command must be issued to restore the states as if before starting the transaction.

SET AUTOCOMMIT- Use the statement SET AUTOCOMMIT to disable auto-commit at the start of the transaction and enable it at the end of the transaction. Use only in case START TRANSACTION or BEGIN or BEGIN WORK are not used to handle the transaction.

Money Transfer Example

I will explain the transaction using the intra-bank money transfer example in which a certain amount of money has to be transferred from one account to another account within the same bank.

Notes: This example is just for demonstration purpose and the actual scenario will definitely be different based on the banking rules. It also assumes that the transactional queries are handle programatically and the intermediate values are store in appropraite variables.

The sequence of operations to perform the transfer is as follows:

1. Obtain the debit and credit customer ids from the request and store in variables.
2. Obtain the amount to be trasferred from the request and store in a variable.
3. Start the transaction.
4. Obtain the balance of first customer and store in a variable.
5. Obtain the balance of second customer and store in a variable.
6. Rollback the transaction in case the first customer has insufficient balance.
7. Add a debit transaction to reflect the deduction from first customer account.
8. Rollback in case of failure.
9. Add a credit transaction to reflect the transfer to the second customer account.
10. Rollback in case of failure.
11. Record the transfer.
12. Rollback in case of failure.
13. Update the balance of first customer.
14. Rollback in case of failure.
15. Update the balance of second customer.
16. Rollback in case of failure.
17. Commit the transaction. 

The sample queries below are used to execute the transfer sequence.

-- Start the transaction
START TRANSACTION;

-- Get balance of first customer
SELECT balance from ACCOUNT WHERE customer_id = 123124123;

-- Get balance of second customer
SELECT balance from ACCOUNT WHERE customer_id = 223124145;

-- Rollback in case of insufficient funds
ROLLBACK;

-- Add debit transaction
INSERT INTO transaction(customer_id,amount,type,reference) VALUES(123124123, <amount>, 0, <reference>);

-- Rollback in case of failure
ROLLBACK;

-- Add credit transaction
INSERT INTO transaction(customer_id,amount,type,reference) VALUES(223124145, <amount>, 1, <reference>);

-- Rollback in case of failure
ROLLBACK;

-- Add transfer transaction
INSERT INTO transfer(from,to,amount) VALUES(123124123, 223124145, <amount>);

-- Rollback in case of failure
ROLLBACK;

-- Update balance of first customer
UPDATE ACCOUNT SET balance = <balance - amount> WHERE customer_id = 123124123;

-- Rollback in case of failure
ROLLBACK;

-- Update balance of second customer
UPDATE ACCOUNT SET balance = <balance + amount> WHERE customer_id = 223124145;

-- Rollback in case of failure
ROLLBACK;

-- Commit the transaction
COMMIT;

We can clearly see that we must rollback in case of failure at any stage to return back to initial states before starting the transfer to reflect the actual balance of both the customers.

This is how we can handle the transactions in MySQL.