MySQL Transactions

A transaction is a sequence of SQL operations executed as a single unit of work.
Transactions ensure data integrity by allowing you to group multiple queries together and either
commit them all at once or roll them back if something goes wrong.
They are especially important in banking systems, order processing, and other critical applications.

ACID Properties of Transactions

• Atomicity: All operations succeed, or none do (all-or-nothing).
• Consistency: Ensures data remains valid before and after the transaction.
• Isolation: Transactions execute independently without interfering with each other.
• Durability: Once committed, changes are permanent even in case of system failure.

Basic Transaction Syntax

START TRANSACTION;
-- SQL statements

COMMIT;     -- to save changes
-- OR
ROLLBACK;   -- to undo changes

Example:

CREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  name VARCHAR(100),
  balance DECIMAL(10, 2)
);

INSERT INTO accounts VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 1500.00);