MySQL – Database Transaction

MySQL Transaction tutorial for Beginners | CreativeDev
image source: http://www.thecreativedev.com/mysql-transaction-tutorial-for-beginners/

A transaction is a sequence of database operations that satisfies the ACID properties (which can be perceived as a single logical operation on the data). Transactions guarantee validity of the data even in the event of errors, power failures etc.

MySQL fully satisfies the ACID properties for transaction operations in the RDBMS.

ACID is an acronym that stands for:

  • A – Atomicity
  • C – Consistency
  • I – Isolation
  • D – Durability

Atomicity:

A transaction is an atomic unit, all instructions within a transaction will successfully execute or none of them will execute. MySQL handles this by storing the results of transactional statements in a memory buffer and writes to disc only when the transaction is committed.

Consistency:

A database is initially in a consistent state before a transaction starts, in the event of an error/failure during the transaction, the database must return to its consistent state. This is primarily handled by MySQL logging mechanisms which record all changes to the database and provide an audit trail for transaction recovery.

Isolation:

If two transactions are running concurrently, they should not be affected by each other. i.e, the result should be the same as the result obtained if the transactions ran sequentially. For example if transaction A and transaction B starts concurrently, A accesses property X, modifies it and then transaction B accesses property X, transaction B should get the modified value done by transaction A. The implementation detail depends on the MySQL engine, for example, InnoDB engine uses fine-grained row-level locking for this purpose.

Durability:

Changes that have been committed should remain even in the case of software and hardware failure. MySQL implements durability by maintaining a transaction log file that tracks changes to the system during the course of a transaction.

REFERENCES: