PHP MySQL Transaction

PHP MySQL: Transactions

Transactions in MySQL allow you to execute a series of queries as a single unit of work. If any query fails, you can roll back the entire transaction to maintain data integrity. Transactions are especially useful in scenarios where multiple related operations must succeed or fail together.

Steps to Use Transactions

1. Start a Transaction
2. Execute Queries
3. Commit the Transaction (if all queries succeed)
4. Roll Back the Transaction (if any query fails)

Example Scenario

Imagine you're implementing a banking application where money is transferred between two accounts. The process involves:

1. Deducting the amount from the sender's account.
2. Adding the amount to the receiver's account.

Both operations must succeed, or neither should happen.

Code Example: Using MySQL Transactions

1. Establish a Database Connection

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "bank_db";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

2. Perform a Transaction

<?php
// Start the transaction
$conn->begin_transaction();

try {
    // Deduct amount from sender's account
    $sender_id = 1;
    $receiver_id = 2;
    $amount = 100;

    $sql1 = "UPDATE accounts SET balance = balance - $amount WHERE id = $sender_id";
    if (!$conn->query($sql1)) {
        throw new Exception("Error updating sender's account: " . $conn->error);
    }

    // Add amount to receiver's account
    $sql2 = "UPDATE accounts SET balance = balance + $amount WHERE id = $receiver_id";
    if (!$conn->query($sql2)) {
        throw new Exception("Error updating receiver's account: " . $conn->error);
    }

    // Commit the transaction
    $conn->commit();
    echo "Transaction completed successfully!";
} catch (Exception $e) {
    // Roll back the transaction if any query fails
    $conn->rollback();
    echo "Transaction failed: " . $e->getMessage();
}

// Close the connection
$conn->close();
?>

Explanation

1. Start a Transaction

   • Use $conn->begin_transaction() to start a new transaction.

2. Execute Queries

   • Perform the required queries within the try block.
   • If a query fails, an exception is thrown.

3. Commit or Roll Back

   • If all queries succeed, use $conn->commit() to save the changes.
   • If any query fails, use $conn->rollback() to undo all changes.

4. Error Handling

   • Exceptions are caught in the catch block to handle errors and roll back the transaction.

Using Prepared Statements in Transactions

<?php
$conn->begin_transaction();

try {
    $stmt1 = $conn->prepare("UPDATE accounts SET balance = balance - ? WHERE id = ?");
    $stmt1->bind_param("di", $amount, $sender_id);

    $stmt2 = $conn->prepare("UPDATE accounts SET balance = balance + ? WHERE id = ?");
    $stmt2->bind_param("di", $amount, $receiver_id);

    // Deduct from sender
    $amount = 100;
    $sender_id = 1;
    if (!$stmt1->execute()) {
        throw new Exception("Error updating sender's account: " . $stmt1->error);
    }

    // Add to receiver
    $receiver_id = 2;
    if (!$stmt2->execute()) {
        throw new Exception("Error updating receiver's account: " . $stmt2->error);
    }

    // Commit the transaction
    $conn->commit();
    echo "Transaction completed successfully!";
} catch (Exception $e) {
    $conn->rollback();
    echo "Transaction failed: " . $e->getMessage();
}

// Close statements and connection
$stmt1->close();
$stmt2->close();
$conn->close();
?>

Key Notes

1. Autocommit Mode

   • MySQL automatically commits changes by default. To use transactions, turn off autocommit using $conn->begin_transaction().

2. Use Exception Handling

   • Always use try-catch to handle errors and ensure a rollback occurs if needed.

3. Ensure Table Support for Transactions

   • Transactions work only with tables using the InnoDB storage engine. Ensure your table is InnoDB with:

     SHOW TABLE STATUS WHERE Name = 'table_name';
     

4. Avoid Deadlocks

   • Minimize the number of queries in a transaction and ensure they’re executed in a consistent order to avoid deadlocks.

5. Test with Edge Cases

   • Test your transaction logic with scenarios like zero balance or simultaneous transfers.

When to Use Transactions

• Financial Applications: E.g., money transfers, withdrawals, or deposits.
• Inventory Management: Ensuring stock levels are updated correctly.
• Batch Updates: Large updates where partial changes can lead to data inconsistency.
• Data Integrity: Enforcing atomicity in critical operations.

Let me know if you need further clarification or examples!