MySQL - Transactions

MySQL - Transactions

What are Transactions?

A transaction is a sequence of database operations performed as a single, logical unit of work. The core principle is “all or nothing”: either every operation within the transaction succeeds and its changes are made permanent, or if any single operation fails, the entire transaction is undone (rolled back), leaving the database in its original state. This guarantees data integrity.

A classic example is a bank transfer: debiting one account and crediting another must happen together. If the credit fails after the debit succeeds, a transaction ensures the initial debit is reversed.

The ACID Properties

Transactions are defined by four standard properties, known by the acronym ACID:

• Atomicity: Ensures that all operations within a transaction are completed as a single, indivisible unit. It’s an “all or nothing” proposition.
• Consistency: Guarantees that a transaction brings the database from one valid state to another. All database rules, such as constraints and triggers, must be satisfied.
• Isolation: Ensures that concurrent transactions do not interfere with each other. The effects of an incomplete transaction are not visible to other transactions.
• Durability: Guarantees that once a transaction has been committed, its changes are permanent and will survive any subsequent system failure, like a power outage or crash.

Transactional Control Statements

MySQL provides several statements to control transactions:

• START TRANSACTION: Begins a new transaction. BEGIN or BEGIN WORK are aliases.
• COMMIT: Makes all changes made during the current transaction permanent.
• ROLLBACK: Discards all changes made during the current transaction.
• SAVEPOINT identifier: Creates a named point within a transaction to which you can later roll back.
• ROLLBACK TO SAVEPOINT identifier: Rolls the transaction back to a named savepoint without ending the entire transaction.
• RELEASE SAVEPOINT identifier: Removes a named savepoint.
• SET AUTOCOMMIT = {0 | 1}: Disables or enables the default autocommit mode for the current session. By default, AUTOCOMMIT is 1 (ON), meaning every SQL statement is its own transaction and is committed immediately. To manage transactions manually, you must set SET AUTOCOMMIT = 0; or begin with START TRANSACTION.

Example: COMMIT and ROLLBACK

Using the Customers table from the previous chapter, let’s demonstrate a transaction. We’ll start a transaction, delete a record, and then change our minds and roll it back.

-- 1. Start the transaction
START TRANSACTION;

-- 2. Delete customers with age 25
DELETE FROM Customers WHERE age = 25;

-- At this point, if you check from another connection, the rows will still be there.
-- The changes are only visible within this session.

-- 3. We decide to undo the change
ROLLBACK;

-- Now, if we check the table, the customers with age 25 are back.
SELECT * FROM Customers WHERE age = 25;

If we had used COMMIT instead of ROLLBACK, the deletion would have become permanent.

Example: SAVEPOINT

Savepoints allow for partial rollbacks.

START TRANSACTION;

-- Operation 1
DELETE FROM Customers WHERE id = 1;
SAVEPOINT after_delete_1;

-- Operation 2
DELETE FROM Customers WHERE id = 2;
SAVEPOINT after_delete_2;

-- Operation 3
UPDATE Customers SET salary = salary * 1.1 WHERE id = 3;

-- Now, let's undo the update and the deletion of customer 2
ROLLBACK TO SAVEPOINT after_delete_1;

-- Finalize the remaining changes (only deletion of customer 1)
COMMIT;

Isolation Levels

The ‘I’ in ACID stands for Isolation, which controls how much one transaction is exposed to changes from other concurrent transactions. MySQL (with InnoDB) offers four standard isolation levels, which trade off consistency for performance.

• READ UNCOMMITTED: Lowest level. One transaction can see uncommitted changes from another (a “dirty read”). Rarely used.
• READ COMMITTED: A transaction can only see changes that have been committed. Prevents dirty reads, but can lead to “non-repeatable reads” (a row changes between two reads in the same transaction).
• REPEATABLE READ: The default level in InnoDB. Once a transaction reads a row, it will see the same data for that row if it reads it again, even if another transaction has since committed changes to it. However, it can still experience “phantom reads” (new rows inserted by another transaction appear in later reads).
• SERIALIZABLE: Highest level. It effectively places a read lock on all rows it touches, preventing other transactions from modifying them. This prevents all concurrency issues, including phantom reads, but at a high cost to performance.

You can set the isolation level for the next transaction or for the entire session:

-- Set for the next transaction only
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Set for the entire session
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

Transaction-Safe Storage Engines

Not all MySQL storage engines support transactions. For modern applications, InnoDB is the standard and default choice. It is fully ACID-compliant.

Older engines like MyISAM do not support transactions. Any COMMIT or ROLLBACK commands issued against a MyISAM table will have no effect. When creating a new table, ensure its engine is InnoDB to use transactions.

CREATE TABLE my_transactional_table (
    id INT PRIMARY KEY,
    data VARCHAR(100)
) ENGINE = InnoDB;

Common Pitfalls: Deadlocks and Long Transactions

• Deadlocks: A deadlock occurs when two or more transactions are waiting for each other to release locks. For example, Transaction A locks row 1 and waits for row 2, while Transaction B locks row 2 and waits for row 1. InnoDB automatically detects deadlocks, rolls back one of the transactions (the “victim”), and returns an error. Your application code should be prepared to catch this error and retry the transaction.
• Long-Running Transactions: A transaction that remains open for a long time can hold locks on resources, blocking other transactions and consuming system resources. Always aim to keep transactions as short as possible.

Using Transactions in a Client Program

Properly managing transactions is a critical part of application development. The general pattern is: start transaction, run queries in a try block, commit if all succeed, and roll back in a catch block if any query fails.

PHP
NodeJS
Java
Python

PHP
NodeJS
Java
Python



// Using PDO for transaction management
// (Connection setup is the same as the stored procedure example)

try {
    // 1. Start a transaction
    $pdo->beginTransaction();
    echo "Transaction started.\n";

    // 2. Perform operations
    $sql1 = "UPDATE Customers SET salary = salary - 500 WHERE id = 1";
    $pdo->exec($sql1);
    echo "Debited 500 from customer 1.\n";

    $sql2 = "UPDATE Customers SET salary = salary + 500 WHERE id = 2";
    $pdo->exec($sql2);
    echo "Credited 500 to customer 2.\n";

    // 3. If everything is fine, commit the transaction
    $pdo->commit();
    echo "Transaction committed successfully!\n";

} catch (\PDOException $e) {
    // 4. If any error occurs, roll back the transaction
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
        echo "Transaction rolled back due to an error.\n";
    }
    echo "Error: " . $e->getMessage();
}




// Using mysql2 with promises and async/await
const mysql = require('mysql2/promise');

async function performTransaction() {
    let connection;
    try {
        connection = await mysql.createConnection({
            host: 'localhost',
            user: 'root',
            password: 'password',
            database: 'TUTORIALS'
        });

        // 1. Start a transaction
        await connection.beginTransaction();
        console.log('Transaction started.');

        // 2. Perform operations
        await connection.execute('UPDATE Customers SET salary = salary - 500 WHERE id = ?', [1]);
        console.log('Debited 500 from customer 1.');

        // To simulate an error, uncomment the next line:
        // throw new Error('Simulated application error!');

        await connection.execute('UPDATE Customers SET salary = salary + 500 WHERE id = ?', [2]);
        console.log('Credited 500 to customer 2.');

        // 3. If all good, commit
        await connection.commit();
        console.log('Transaction committed successfully!');

    } catch (error) {
        console.error('An error occurred:', error.message);
        // 4. If an error occurred, roll back
        if (connection) {
            await connection.rollback();
            console.log('Transaction rolled back.');
        }
    } finally {
        if (connection) {
            await connection.end();
            console.log('Connection closed.');
        }
    }
}

performTransaction();




// Using modern JDBC with try-with-resources and manual commit control
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TransactionExample {
    private static final String URL = "jdbc:mysql://localhost:3306/TUTORIALS";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        // try-with-resources ensures the connection is closed
        try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD)) {
            // 1. Disable auto-commit to start a transaction
            conn.setAutoCommit(false);
            System.out.println("Transaction started.");

            try (Statement stmt = conn.createStatement()) {
                // 2. Perform operations
                stmt.executeUpdate("UPDATE Customers SET salary = salary - 500 WHERE id = 1");
                System.out.println("Debited 500 from customer 1.");

                stmt.executeUpdate("UPDATE Customers SET salary = salary + 500 WHERE id = 2");
                System.out.println("Credited 500 to customer 2.");

                // 3. If all good, commit
                conn.commit();
                System.out.println("Transaction committed successfully!");

            } catch (SQLException e) {
                // 4. If any error, roll back
                System.out.println("An error occurred, rolling back transaction.");
                conn.rollback();
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}




# Using mysql-connector-python's built-in transaction methods
import mysql.connector
from mysql.connector import errorcode

def main():
    config = {
        'user': 'root',
        'password': 'password',
        'host': '127.0.0.1',
        'database': 'TUTORIALS'
    }
    connection = None
    try:
        connection = mysql.connector.connect(**config)
        cursor = connection.cursor()

        # 1. Start a transaction
        # In mysql-connector-python, transactions start automatically on the first DML statement.
        # You control it with commit() and rollback().
        print("Transaction started implicitly.")

        # 2. Perform operations
        cursor.execute("UPDATE Customers SET salary = salary - 500 WHERE id = %s", (1,))
        print("Debited 500 from customer 1.")

        cursor.execute("UPDATE Customers SET salary = salary + 500 WHERE id = %s", (2,))
        print("Credited 500 to customer 2.")

        # 3. If all is well, commit
        connection.commit()
        print("Transaction committed successfully!")

    except mysql.connector.Error as err:
        print(f"An error occurred: {err}")
        # 4. If an error occurred, roll back
        if connection:
            print("Rolling back transaction.")
            connection.rollback()
    finally:
        if connection and connection.is_connected():
            cursor.close()
            connection.close()
            print("Connection closed.")

if __name__ == "__main__":
    main()