MySQL Transaction

A transaction in MySQL is a sequence of one or more SQL statements that are treated as a single, indivisible unit of work. Transactions are used to ensure the integrity of data in a relational database by following the principles of ACID (Atomicity, Consistency, Isolation, Durability).

Here are the key concepts related to MySQL transactions:

1. BEGIN: To start a transaction, you use the BEGIN statement (or START TRANSACTION).

   BEGIN;

2. COMMIT: To save the changes made during a transaction to the database, you use the COMMIT statement.

   COMMIT;

3. ROLLBACK: If something goes wrong during a transaction and you want to discard the changes made so far, you can use the ROLLBACK statement.

   ROLLBACK;

4. AUTOCOMMIT: By default, MySQL operates in autocommit mode, which means that each SQL statement is treated as a separate transaction. You can disable autocommit to group multiple statements into a single transaction.

   SET autocommit = 0;  -- Disable autocommit

5. SAVEPOINT: You can set savepoints within a transaction to create points to which you can later roll back.

   SAVEPOINT savepoint_name;

6. ROLLBACK TO: To roll back to a savepoint within a transaction, you use the ROLLBACK TO statement.

   ROLLBACK TO savepoint_name;

Here’s a simple example of a MySQL transaction:

BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

SAVEPOINT before_insert;
INSERT INTO transactions (account_id, amount, transaction_type)
VALUES (1, 100, 'Withdrawal');

-- Check for errors
IF some_error_condition THEN
    ROLLBACK TO before_insert; -- Roll back to the savepoint
ELSE
    COMMIT; -- If no errors, commit the changes
END IF;

we begin a transaction, update the account balance, insert a transaction record, and then decide whether to commit the transaction or roll back to a savepoint based on certain conditions.

Transactions are essential for maintaining data integrity, especially in situations where multiple related tables need to be updated together, and ensuring that changes are atomic and consistent is crucial.