Types of Transaction in MySQL

In this article we will learn types of transaction in MySQL. Transaction is a set of SQL statements that are executed as if they were one statement.

Types of Transaction in MySQL

These are the types of MySQL transaction:

What Is Transaction?

Transaction is a set of SQL statements that are executed as if they were one statement. For a transaction to be finished and save data changes permanently. All the statements in the transaction have to be completed. If a transaction is not completed for any reason, the changes to the data-set that the transaction already made are removed,placing the database in its original state before the transaction began.

START TRANSACTION;
UPDATE checking SET balance = balance – 1000 WHERE id = 145356;
UPDATE savings SET balance = balance + 1000 WHERE id = 118254;
COMMIT;

What is ACID in Transaction?

A transaction is a transaction only if it is ACID-compliant.

A  –  Atomicity

C –  Consistency

I –   Isolation

D – Durability.

For any transaction we need to follow the rule of transaction.

Atomicity

Atomicity refers to concept that either all of the statements inside a transaction are completed, or none of them are performed. In a banking system, the transfer of funds can be completed or it could fail. However, the transfer of funds is not allowed to fail leaving the work half-done.
The atomicity property guarantees that one account will not be debited unless the other account is credited. Each transaction is said to be atomic (indivisible) — even though there are actually two statements.They act as if they are one statement. If any part of the transaction fails, the entire transaction must fail.

Consistency

The consistency property ensures that the database moves from one consistent state to another.If the server were to fail while executing the transfer of money from Adam’s account to Oliver’s account, the database would be left in an inconsistent state. The only way to resolve this inconsistency is to undo changes already made. Before a transaction begins the database should be in a consistent state. Once the transaction either completes successfully or is rolled back the database should still be in a consistent state.
In a transaction system (ACID-compliant by definition), if one or more statements in the transaction do not succeed.The entire transaction must be rolled back to a consistent state.If a transaction is successful, the database moves from one consistent state to another.

Isolation

The isolation property specifies that data being modified for one transaction cannot be viewed or modified by a second transaction until the completion of the first transaction. This is important to support concurrent execution of queries, which is critical in any modern database system.
With isolation, separate transactions can run at the same time without compromising the consistency of the data. In MySQL, the level of isolation that transactions have can be configured.

Durability

Durability describes the principle that once a transaction has been successfully completed, the results are recorded by the database server permanently. After this point the transaction is complete, and the data changes must survive even if the database or operating system fails.
If there is an operating system failure between the time that a transaction successfully completes.The time the data is actually written to the disk permanently, the database has marked the transaction as complete but the data has not been changed appropriately.
Many databases implement durability by writing complete transactions into a log that can be played back to re-create the database state right before a failure. A transaction is considered successfully committed only after it has been written to this log, called a redo log.

The above details are just basic of transaction. I will explain more in my next article about commit,rolleback etc.

Thanks for reading. If you have any query please right to us, or comment on article.