19 - MySQL Transactions

Transaction is a group of queries or operations performed at a time on database. Transaction in terms of database is related to commit the results over disk or rollback the results in case of failure. Sometime it is necessary to either run all the queries or no single query. A very basic example is the case of banking transaction in which some particular amount is debited from an account and is credited into another account. The simple queries as:

UPDATE accountTable SET balance=balance- 1500 where accountNo = 1;
UPDATE accountTable SET balance=balance+ 1500 where accountNo = 2;

So we need to make sure that either both queries run or none of the query runs so we combined them into a single query to make consistency and that is called transaction. So the basic syntax in MySQL to create a transaction is:

START Transaction;
UPDATE accountTable SET balance=balance- 1500 where accountNo = 1;
UPDATE accountTable SET balance=balance+ 1500 where accountNo = 2;

Here commit writes the data to disk for both transaction at a once and keeps the data in buffer to rollback in case of failure.

There are some queries in MySQL which are Auto-commit and can’t be roll back. These queries include CREATE, DROP and ALTER for all functions, tables, procedures, databases, events and Indexes.

Note: MySQL provides a configuration parameter called autocommit having two possible values (0,1) which indicates autocommit on or off.Autocommit is a session variable. In case of autocommit off MySQL writes all the data changes into log files and not to the disk until COMMIT command is executed. We can set it by the simple SET command:

SET autocommit = 0;  or  SET autocommit = 1;

By default autocommit is enabled but when we start the transaction it is disabled for that transaction only.

Basic properties of a Transaction

The properties of a transaction in MySQL are abbreviated as ACID (Atomicity, consistency, isolation and durability).

Atomicity: Atom is the smallest unit it is either completely exist or not at all. In the same way queries that are grouped into a transaction either runs completely or not run at all. If a single query fails then the whole transaction rolls back.

Consistency: Data is updated and visible to users only after complete execution of the transaction, no intermediate values seen by any user which may be rolled back later during transaction.

Isolation: Transactions are independent from each other, no other transaction or query can fetch the data or value from another transaction until first transaction is complete.

Durability: Ensures that the result always writes into the disk even in the case of system failure. In case of failure it dealt with logs to either redo or undo the whole transaction.

Transaction Keywords

COMMIT: For confirming the operation and writes the result into the disk from the buffers / logs.

ROLLBACK: It persist the data for all tables to its original state if the transaction fails in the middle of its execution.

NOTE: INNODB engine is the transactional safe engine so always use INNODB in case of applications where you need ACID compliance.

Like us on Facebook