MySQL Transaction

Transactions are units or sequences of work performed against an either in a manual fashion by a user or automatically by some sort of a database program. In a relational database using SQL, transactions are accomplished using the Data Manipulation Language (DML).

Commands that enable the user to manage database transactions:

1. COMMIT: Saves database transactions 

2. ROLLBACK: Undoes database transactions 

3. SAVEPOINT: Creates points within groups of transactions in which to ROLLBACK 

4. SET TRANSACTION: Places a name on a transaction 

Transactional control commands are necessary for only DML (Data Manipulation Language) commands INSERT, UPDATE, and DELETE. DDL (Data Definition Language) commands like CREATE TABLE, DROP TABLE automatically committed to the database.

When a transaction has completed, all series of database operations are stored either in an allocated area or in a temporary rollback area in the database as a single unit.  All changes are held in this temporary rollback area until a transactional control command is issued. When commit command is issued then changes are saved to the database. When rollback command is issued the changes are discarded. Below figure illustrates how changes are applied to a relational database.

 Database will not ensure atomicity property. There is no guarantee that multiple database operations will run as one atomic unit with “all or nothing” criteria. In a set of operations if once operation fails then database will continues to execute next operations.

Database will not ensure consistency property. If any error occurs in a transaction and it fails, then any changes already made will be saved to database if COMMIT command is issued. It means that a database administrator needs to clean up any mismatched data from operations that fail to complete.

Database will not ensure isolation property. There can be one or more transactions running concurrently which may access the same data at the same time. Multiple transactions can run without knowing each other. Each transaction has no lock on the database objects, and the changes made by one transaction may not reflect in other transaction.

Database transactions may face concurrency problems like Dirty read, Repeatable read and Phantom read. A dirty read allows a row changed by one transaction to be read by another transaction before any changes in the row have been committed (a "dirty read"). A Repeatable read is where one transaction reads a row, a second transaction alters or deletes the row and commits, and the first transaction re-reads the row, getting different values the second time. A phantom read is where one transaction reads (T1) all rows that satisfy a WHERE condition, a second transaction (T2) inserts a row that satisfies that WHERE condition, and the first transaction (T1) re-reads for the same condition retrieving the additional “phantom row” in the second read.