Transactions and ACID Properties:

• Atomicity ensures that a transaction is treated as a single unit of work, meaning it either fully succeeds or fails.

  If any part of the transaction fails, the entire transaction is rolled back to its original state.

• Consistency ensures that the database remains in a consistent state before and after a transaction.

  All data modifications must adhere to the defined rules and constraints of the database.

• Isolation ensures that the execution of multiple transactions concurrently does not interfere with each other.

  Each transaction operates independently, as if it were the only transaction running on the database.

• Durability is one of the important properties of databases, and once a record has been updated, it is intact for the lifetime of the databases even when the system fails unexpectedly.

  The alterations are permanently stored and both unable to be lost even in the circumstance of power outages or system shut-down.

Performing Transactions in MySQL:

• Starting a Transaction:

  Use the BEGIN or START TRANSACTION statement to begin a transaction.

• Executing SQL Statements:

  Execute the desired SQL statements within the transaction.

• Committing a Transaction:

  With COMMIT statement apply the made by a transaction changes durably to its database.

• Rolling Back a Transaction:

  The ROLLBACK action, may be used to cancel the transaction and bring back the original condition of the database.

Example of Transaction in MySQL:

                                  
BEGIN;
UPDATE account SET balance = balance - 100 WHERE account_id = 123;
UPDATE account SET balance = balance + 100 WHERE account_id = 456;
COMMIT;
                                  
                                

In such transaction, $100 is deducted from account 123 and added to account 456. If the process of the updates will fail even one of the trials, the whole transaction will be rolled back making sure atomicity.