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
- Start a Transaction
- Execute Queries
- Commit the Transaction (if all queries succeed)
- 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:
- Deducting the amount from the sender's account.
- 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
2. Perform a Transaction
Explanation
Start a Transaction
- Use
$conn->begin_transaction()
to start a new transaction.
- Use
Execute Queries
- Perform the required queries within the
try
block. - If a query fails, an exception is thrown.
- Perform the required queries within the
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.
- If all queries succeed, use
Error Handling
- Exceptions are caught in the
catch
block to handle errors and roll back the transaction.
- Exceptions are caught in the
Using Prepared Statements in Transactions
Key Notes
Autocommit Mode
- MySQL automatically commits changes by default. To use transactions, turn off autocommit using
$conn->begin_transaction()
.
- MySQL automatically commits changes by default. To use transactions, turn off autocommit using
Use Exception Handling
- Always use
try-catch
to handle errors and ensure a rollback occurs if needed.
- Always use
Ensure Table Support for Transactions
- Transactions work only with tables using the InnoDB storage engine. Ensure your table is InnoDB with:
- Transactions work only with tables using the InnoDB storage engine. Ensure your table is InnoDB with:
Avoid Deadlocks
- Minimize the number of queries in a transaction and ensure they’re executed in a consistent order to avoid deadlocks.
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!