Introduction
Database transactions help PHP applications perform multiple database operations safely as one logical unit. If every operation succeeds, the changes can be saved using COMMIT. If something goes wrong, ROLLBACK can undo the transaction. In this chapter, you will practise transactions, error handling and database recovery using MySQLi and PDO. Database Transactions and Error Handling PHP Practice Questions with Solutions to help you to understand the concepts.
Database Setup
Before starting the questions, create a database named bank_db and two tables: accounts and transactions.
SQL Table Structure
CREATE DATABASE bank_db;
USE bank_db;
CREATE TABLE accounts (
id INT AUTO_INCREMENT PRIMARY KEY,
account_holder VARCHAR(100),
balance DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE transactions (
id INT AUTO_INCREMENT PRIMARY KEY,
from_account INT,
to_account INT,
amount DECIMAL(10,2),
transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
Insert Sample Records
INSERT INTO accounts (account_holder, balance)
VALUES
('Rahul', 10000.00),
('Priya', 8000.00),
('Amit', 5000.00);
Initial Account Records
| ID | Account Holder | Balance |
|---|---|---|
| 1 | Rahul | 10,000.00 |
| 2 | Priya | 8,000.00 |
| 3 | Amit | 5,000.00 |
The following questions use these tables.
Understanding Database Transactions
A transaction is a group of database operations that should be treated as one complete unit.
For example, suppose Rahul wants to transfer ₹2,000 to Priya.
Two operations are required:
- Deduct ₹2,000 from Rahul’s account.
- Add ₹2,000 to Priya’s account.
Both operations must succeed. If money is deducted from Rahul but not added to Priya, the database becomes inconsistent.
A transaction helps prevent this problem.
Important Transaction Commands
| Command | Meaning |
|---|---|
START TRANSACTION | Begins a transaction |
COMMIT | Saves all transaction changes |
ROLLBACK | Cancels uncommitted changes |
SAVEPOINT | Creates a point inside a transaction |
ROLLBACK TO SAVEPOINT | Reverses changes made after a savepoint |
ACID Properties
Database transactions follow four important properties called ACID.
| Property | Meaning | Simple Example |
|---|---|---|
| Atomicity | All operations succeed or none are saved | Money is deducted and credited together |
| Consistency | Data remains valid before and after a transaction | Account balance cannot become negative under the application’s rules |
| Isolation | Concurrent transactions should not interfere incorrectly | Two transfers should not overwrite each other’s changes |
| Durability | Committed changes remain saved | A completed transfer remains recorded after a restart |
Question 1: Start and Commit a Database Transaction Using MySQLi
Problem Statement
A bank wants to transfer ₹1,000 from Rahul’s account to Priya’s account.
Create a PHP program using MySQLi that:
- Starts a transaction.
- Deducts ₹1,000 from Rahul’s account.
- Adds ₹1,000 to Priya’s account.
- Commits the transaction if both operations succeed.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "bank_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$conn->begin_transaction();
try {
// Deduct money from Rahul
$sql1 = "UPDATE accounts
SET balance = balance - ?
WHERE id = ? AND balance >= ?";
$stmt1 = $conn->prepare($sql1);
$amount = 1000;
$fromAccount = 1;
$stmt1->bind_param(
"didi",
$amount,
$fromAccount,
$amount
);
$stmt1->execute();
if ($stmt1->affected_rows !== 1) {
throw new Exception("Unable to debit sender account.");
}
$stmt1->close();
// Add money to Priya
$sql2 = "UPDATE accounts
SET balance = balance + ?
WHERE id = ?";
$stmt2 = $conn->prepare($sql2);
$toAccount = 2;
$stmt2->bind_param("di", $amount, $toAccount);
$stmt2->execute();
if ($stmt2->affected_rows !== 1) {
throw new Exception("Unable to credit receiver account.");
}
$stmt2->close();
// Save both operations
$conn->commit();
echo "Transaction completed successfully!";
} catch (Throwable $e) {
$conn->rollback();
echo "Transaction failed. All changes were rolled back.";
}
$conn->close();
?>
Sample Output
Transaction completed successfully!
Explanation
begin_transaction()starts a new transaction.- The first UPDATE query deducts money from Rahul.
- The WHERE condition checks that Rahul has enough balance.
- The second UPDATE query adds money to Priya.
commit()permanently saves both changes.- If an operation fails, an exception is thrown.
rollback()cancels the uncommitted changes.
The two account updates are treated as one logical operation.
Concepts Covered
- MySQLi transactions
begin_transaction()commit()rollback()- Prepared statements
- Atomic database operations
Question 2: Rollback a Transaction When an Operation Fails
Problem Statement
A bank wants to transfer ₹20,000 from Rahul’s account to Priya’s account.
Rahul has only ₹10,000, so the transfer should not be completed.
Create a PHP program using MySQLi that:
- Starts a transaction.
- Attempts to deduct ₹20,000 from Rahul.
- Checks whether the deduction succeeded.
- Rolls back the transaction if the operation fails.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "bank_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$conn->begin_transaction();
try {
$amount = 20000;
$fromAccount = 1;
$sql = "UPDATE accounts
SET balance = balance - ?
WHERE id = ? AND balance >= ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param(
"did",
$amount,
$fromAccount,
$amount
);
$stmt->execute();
if ($stmt->affected_rows !== 1) {
throw new Exception("Insufficient balance.");
}
$stmt->close();
$conn->commit();
echo "Transaction completed successfully!";
} catch (Throwable $e) {
$conn->rollback();
echo "Transaction failed: " . $e->getMessage();
}
$conn->close();
?>
Sample Output
Transaction failed: Insufficient balance.
Explanation
Rahul’s account contains ₹10,000, but the program attempts to deduct ₹20,000.
The SQL query includes this condition:
WHERE id = ? AND balance >= ?
This ensures that the account is updated only when the available balance is sufficient.
Because the condition is not satisfied, no row is changed. The program throws an exception and executes rollback().
The account balance remains ₹10,000.
Concepts Covered
- Transaction rollback
- Insufficient balance checking
- Conditional UPDATE
- Exception handling
- Preventing invalid account changes
Question 3: Use PDO Transactions with COMMIT
Problem Statement
A bank wants to transfer ₹500 from Priya’s account to Amit’s account.
Create a PHP program using PDO that:
- Begins a transaction.
- Deducts ₹500 from Priya.
- Adds ₹500 to Amit.
- Commits the transaction if both operations succeed.
PHP Solution
<?php
try {
$conn = new PDO(
"mysql:host=localhost;dbname=bank_db;charset=utf8mb4",
"root",
""
);
$conn->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
$conn->beginTransaction();
$amount = 500;
// Deduct from Priya
$sql1 = "UPDATE accounts
SET balance = balance - :amount
WHERE id = :id AND balance >= :amount";
$stmt1 = $conn->prepare($sql1);
$stmt1->execute([
":amount" => $amount,
":id" => 2
]);
if ($stmt1->rowCount() !== 1) {
throw new Exception("Unable to debit Priya's account.");
}
// Add to Amit
$sql2 = "UPDATE accounts
SET balance = balance + :amount
WHERE id = :id";
$stmt2 = $conn->prepare($sql2);
$stmt2->execute([
":amount" => $amount,
":id" => 3
]);
if ($stmt2->rowCount() !== 1) {
throw new Exception("Unable to credit Amit's account.");
}
$conn->commit();
echo "Money transferred successfully!";
} catch (Throwable $e) {
if (isset($conn) && $conn->inTransaction()) {
$conn->rollBack();
}
echo "Transaction failed. Changes were cancelled.";
}
?>
Sample Output
Money transferred successfully!
Explanation
PDO provides three important transaction methods:
beginTransaction()starts the transaction.commit()saves the transaction changes.rollBack()cancels uncommitted changes.
The first query deducts money from Priya. The second query credits Amit.
The program commits only after both operations succeed. If an exception occurs, the catch block rolls back the transaction.
The inTransaction() method checks whether a transaction is currently active before attempting a rollback.
Concepts Covered
- PDO transactions
beginTransaction()commit()rollBack()inTransaction()- Exception handling
Question 4: Handle Database Connection Errors Using MySQLi
Problem Statement
Create a PHP program that connects to a database named bank_db.
If the connection fails, display a friendly error message instead of exposing technical database information.
PHP Solution
<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
try {
$conn = new mysqli(
"localhost",
"root",
"",
"bank_db"
);
$conn->set_charset("utf8mb4");
echo "Database connected successfully!";
$conn->close();
} catch (mysqli_sql_exception $e) {
error_log("Database connection error: " . $e->getMessage());
echo "Unable to connect to the database. Please try again later.";
}
?>
Sample Output
For a successful connection:
Database connected successfully!
If the connection fails:
Unable to connect to the database. Please try again later.
Explanation
mysqli_report()enables MySQLi error reporting.MYSQLI_REPORT_ERRORenables error reporting.MYSQLI_REPORT_STRICTmakes MySQLi throw exceptions for reported errors.trycontains the database connection code.catchhandles database exceptions.error_log()records technical details in the server’s error log.- The visitor receives a simple and safe message.
Displaying raw database errors to visitors can expose sensitive technical information. In production applications, log detailed errors privately and show users a general message.
Concepts Covered
- MySQLi connection handling
mysqli_report()try-catchmysqli_sql_exceptionerror_log()- Safe error messages
Question 5: Handle Database Errors Using PDO
Problem Statement
Create a PHP program that connects to bank_db using PDO.
If the connection or database operation fails, catch the exception and display a friendly message.
PHP Solution
<?php
try {
$conn = new PDO(
"mysql:host=localhost;dbname=bank_db;charset=utf8mb4",
"root",
""
);
$conn->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
echo "PDO database connection successful!";
$conn = null;
} catch (PDOException $e) {
error_log("PDO database error: " . $e->getMessage());
echo "Database operation failed. Please try again later.";
}
?>
Sample Output
For a successful connection:
PDO database connection successful!
If the connection fails:
Database operation failed. Please try again later.
Explanation
new PDO()creates the database connection.PDO::ATTR_ERRMODEconfigures error handling.PDO::ERRMODE_EXCEPTIONtells PDO to throw exceptions when database operations fail.trycontains the database operations.catch (PDOException $e)catches PDO-related exceptions.error_log()stores technical error details privately.$conn = nullreleases the connection.
PDO exceptions make it easier to handle database failures using a single try-catch structure.
Concepts Covered
- PDO error handling
PDOExceptionPDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTIONtry-catch- Error logging
Question 6: Use a Transaction to Insert Account and Transaction Records
Problem Statement
A bank wants to open a new account and create its first transaction record.
Both operations must succeed together.
Create a PHP program that:
- Inserts a new account for Rohan with ₹15,000.
- Inserts a transaction record showing the initial deposit.
- Commits both operations if successful.
- Rolls back both operations if an error occurs.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "bank_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$conn->begin_transaction();
try {
// Insert new account
$sql1 = "INSERT INTO accounts (account_holder, balance)
VALUES (?, ?)";
$stmt1 = $conn->prepare($sql1);
$name = "Rohan";
$amount = 15000;
$stmt1->bind_param("sd", $name, $amount);
$stmt1->execute();
$newAccountId = $conn->insert_id;
$stmt1->close();
// Insert initial deposit record
$sql2 = "INSERT INTO transactions
(from_account, to_account, amount)
VALUES (?, ?, ?)";
$stmt2 = $conn->prepare($sql2);
$fromAccount = 0;
$stmt2->bind_param(
"iid",
$fromAccount,
$newAccountId,
$amount
);
$stmt2->execute();
$stmt2->close();
$conn->commit();
echo "Account and initial deposit created successfully!";
} catch (Throwable $e) {
$conn->rollback();
echo "Unable to create account. All changes were cancelled.";
}
$conn->close();
?>
Sample Output
Account and initial deposit created successfully!
Explanation
This example performs two INSERT operations inside one transaction.
First operation:
A new account is inserted into the accounts table.
Second operation:
A transaction record is inserted into the transactions table.
insert_idretrieves the ID automatically generated for the new account.- The new account ID is used in the transaction record.
commit()saves both records.rollback()cancels both operations if an error occurs.
The example uses account ID 0 to represent an initial deposit in the from_account field. In a real banking application, a dedicated transaction type or separate ledger design would be more appropriate.
Concepts Covered
- Multiple INSERT operations
- Database transactions
insert_idcommit()rollback()- Maintaining related database records
Question 7: Use SAVEPOINT and ROLLBACK TO SAVEPOINT
Problem Statement
A bank wants to perform two separate account updates within one transaction.
- Deduct ₹1,000 from Rahul’s account.
- Create a savepoint.
- Deduct another ₹500 from Rahul’s account.
- Roll back to the savepoint.
- Commit the transaction.
The first deduction should remain, while the second deduction should be cancelled.
PHP Solution
<?php
$conn = new mysqli("localhost", "root", "", "bank_db");
if ($conn->connect_error) {
die("Database connection failed.");
}
$conn->begin_transaction();
try {
$amount = 1000;
$accountId = 1;
// First deduction
$sql = "UPDATE accounts
SET balance = balance - ?
WHERE id = ? AND balance >= ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("did", $amount, $accountId, $amount);
$stmt->execute();
if ($stmt->affected_rows !== 1) {
throw new Exception("First deduction failed.");
}
$stmt->close();
// Create savepoint
$conn->query("SAVEPOINT first_deduction");
// Second deduction
$secondAmount = 500;
$sql = "UPDATE accounts
SET balance = balance - ?
WHERE id = ? AND balance >= ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param(
"did",
$secondAmount,
$accountId,
$secondAmount
);
$stmt->execute();
if ($stmt->affected_rows !== 1) {
throw new Exception("Second deduction failed.");
}
$stmt->close();
// Cancel only the second deduction
$conn->query("ROLLBACK TO SAVEPOINT first_deduction");
// Save the remaining transaction
$conn->commit();
echo "Transaction completed. Second deduction cancelled.";
} catch (Throwable $e) {
$conn->rollback();
echo "Transaction failed. All changes cancelled.";
}
$conn->close();
?>
Sample Output
Transaction completed. Second deduction cancelled.
Explanation
A savepoint creates a specific point inside an active transaction.
- The first deduction reduces Rahul’s balance by ₹1,000.
SAVEPOINT first_deductioncreates a checkpoint.- The second deduction reduces the balance by another ₹500.
ROLLBACK TO SAVEPOINTreverses changes made after the savepoint.- The first deduction remains in the transaction.
commit()saves the remaining transaction changes.
If Rahul’s initial balance is ₹10,000, the final balance becomes ₹9,000.
Concepts Covered
- SAVEPOINT
ROLLBACK TO SAVEPOINT- Partial rollback
- MySQLi transactions
- Transaction checkpoints
Question 8: Handle Duplicate Record Errors Using PDO
Problem Statement
A school database contains an accounts table.
The administrator wants to prevent duplicate account-holder email addresses.
Create a PHP program that:
- Adds a unique email column to the accounts table.
- Attempts to insert an account.
- Handles duplicate email errors using PDO.
SQL Setup
First, add an email column with a unique constraint.
ALTER TABLE accounts
ADD COLUMN email VARCHAR(150) UNIQUE;
PHP Solution
<?php
try {
$conn = new PDO(
"mysql:host=localhost;dbname=bank_db;charset=utf8mb4",
"root",
""
);
$conn->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
$sql = "INSERT INTO accounts
(account_holder, balance, email)
VALUES (:name, :balance, :email)";
$stmt = $conn->prepare($sql);
$stmt->execute([
":name" => "Riya",
":balance" => 12000,
":email" => "riya@gmail.com"
]);
echo "Account created successfully!";
} catch (PDOException $e) {
if (
$e->getCode() == "23000" &&
isset($e->errorInfo[1]) &&
$e->errorInfo[1] == 1062
) {
echo "This email address is already registered.";
} else {
error_log("Database error: " . $e->getMessage());
echo "Unable to create the account.";
}
}
?>
Sample Output
For a new email address:
Account created successfully!
If the email already exists:
This email address is already registered.
Explanation
A unique constraint prevents the database from storing duplicate email addresses.
UNIQUEensures that each email address is different.prepare()prepares the INSERT query.execute()attempts to insert the account.- PDO throws an exception when the database rejects a duplicate value.
- Error code
23000indicates an integrity constraint violation. - MySQL error number
1062identifies a duplicate entry.
The program checks the error details and displays a suitable message.
Concepts Covered
- PDO exception handling
- UNIQUE constraint
- Duplicate record prevention
- SQLSTATE error code
- MySQL error number
- Database integrity
Question 9: Check Account Balance Before a Transaction
Problem Statement
A bank wants to transfer ₹3,000 from Rahul’s account to Priya’s account.
Before transferring the money, the program must check whether Rahul has enough balance.
Use PDO transactions and prepared statements to perform the transfer safely.
PHP Solution
<?php
try {
$conn = new PDO(
"mysql:host=localhost;dbname=bank_db;charset=utf8mb4",
"root",
""
);
$conn->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
$conn->beginTransaction();
$fromAccount = 1;
$toAccount = 2;
$amount = 3000;
// Check sender balance
$sql = "SELECT balance
FROM accounts
WHERE id = :id
FOR UPDATE";
$stmt = $conn->prepare($sql);
$stmt->execute([
":id" => $fromAccount
]);
$sender = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$sender) {
throw new Exception("Sender account not found.");
}
if ($sender["balance"] < $amount) {
throw new Exception("Insufficient balance.");
}
// Deduct from sender
$sql = "UPDATE accounts
SET balance = balance - :amount
WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->execute([
":amount" => $amount,
":id" => $fromAccount
]);
// Credit receiver
$sql = "UPDATE accounts
SET balance = balance + :amount
WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->execute([
":amount" => $amount,
":id" => $toAccount
]);
if ($stmt->rowCount() !== 1) {
throw new Exception("Receiver account not found.");
}
$conn->commit();
echo "Money transferred successfully!";
} catch (Throwable $e) {
if (isset($conn) && $conn->inTransaction()) {
$conn->rollBack();
}
echo "Transfer failed: " . $e->getMessage();
}
?>
Sample Output
If Rahul has enough balance and Priya’s account exists:
Money transferred successfully!
If Rahul does not have enough balance:
Transfer failed: Insufficient balance.
Explanation
This program checks the sender’s balance before making the transfer.
beginTransaction()starts a transaction.SELECT ... FOR UPDATElocks the selected account row until the transaction ends.- The program checks whether the sender account exists.
- It checks whether the balance is sufficient.
- The first UPDATE deducts the amount.
- The second UPDATE credits the receiver.
commit()saves the changes.rollBack()cancels the transaction if an error occurs.
FOR UPDATE helps prevent concurrent transactions from modifying the selected row in conflicting ways. The accounts table must use a transaction-capable storage engine such as InnoDB.
For a production banking application, lock both account rows in a consistent order and also validate the amount and account identifiers.
Concepts Covered
- PDO transactions
- Balance checking
SELECT FOR UPDATE- Row locking
- Prepared statements
- Concurrent database operations
Question 10: Build a Bank Transfer System with Transaction History
Problem Statement
A bank wants a simple transfer system that performs three database operations:
- Deduct money from the sender’s account.
- Add money to the receiver’s account.
- Save the transfer details in the
transactionstable.
All three operations must succeed together.
Create a PHP program using PDO transactions and prepared statements.
PHP Solution
<?php
try {
$conn = new PDO(
"mysql:host=localhost;dbname=bank_db;charset=utf8mb4",
"root",
""
);
$conn->setAttribute(
PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION
);
$conn->beginTransaction();
$fromAccount = 1;
$toAccount = 2;
$amount = 2000;
if (
$amount <= 0 ||
$fromAccount === $toAccount
) {
throw new Exception("Invalid transfer details.");
}
// Check sender balance
$sql = "SELECT balance
FROM accounts
WHERE id = :id
FOR UPDATE";
$stmt = $conn->prepare($sql);
$stmt->execute([
":id" => $fromAccount
]);
$sender = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$sender) {
throw new Exception("Sender account not found.");
}
if ($sender["balance"] < $amount) {
throw new Exception("Insufficient balance.");
}
// Check receiver account
$sql = "SELECT id
FROM accounts
WHERE id = :id
FOR UPDATE";
$stmt = $conn->prepare($sql);
$stmt->execute([
":id" => $toAccount
]);
if (!$stmt->fetch(PDO::FETCH_ASSOC)) {
throw new Exception("Receiver account not found.");
}
// Deduct from sender
$sql = "UPDATE accounts
SET balance = balance - :amount
WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->execute([
":amount" => $amount,
":id" => $fromAccount
]);
// Credit receiver
$sql = "UPDATE accounts
SET balance = balance + :amount
WHERE id = :id";
$stmt = $conn->prepare($sql);
$stmt->execute([
":amount" => $amount,
":id" => $toAccount
]);
// Save transaction history
$sql = "INSERT INTO transactions
(from_account, to_account, amount)
VALUES (:from, :to, :amount)";
$stmt = $conn->prepare($sql);
$stmt->execute([
":from" => $fromAccount,
":to" => $toAccount,
":amount" => $amount
]);
// Commit all three operations
$conn->commit();
echo "Bank transfer completed successfully!<br>";
echo "Transferred Amount: ₹" . number_format($amount, 2);
} catch (Throwable $e) {
if (isset($conn) && $conn->inTransaction()) {
$conn->rollBack();
}
error_log("Bank transfer error: " . $e->getMessage());
echo "Bank transfer failed. No changes were saved.";
}
?>
Sample Output
Bank transfer completed successfully!
Transferred Amount: ₹2,000.00
Explanation
This example combines database transactions, prepared statements and error handling.
Operation 1: Check the sender
The program retrieves the sender’s balance and checks whether enough money is available.
Operation 2: Check the receiver
The program verifies that the receiving account exists.
Operation 3: Deduct money
The UPDATE query deducts the transfer amount from the sender’s account.
Operation 4: Add money
The second UPDATE query adds the same amount to the receiver’s account.
Operation 5: Save transaction history
The INSERT query stores the sender ID, receiver ID and transfer amount.
Operation 6: Commit the transaction
The commit() method saves all three database changes.
If any operation fails, rollBack() cancels all uncommitted changes.
Important: This is an educational example, not a production-ready banking system. Real payment systems also need authentication, authorization, transfer limits, idempotency protection, audit logging, secure account locking and careful handling of concurrent transfers.
Concepts Covered
- Complete database transaction
- PDO prepared statements
- Multiple database operations
- Account balance validation
- Transaction history
- COMMIT and ROLLBACK
- Error handling
Important Database Transaction Methods
| Method | MySQLi | PDO | Purpose |
|---|---|---|---|
| Start transaction | begin_transaction() | beginTransaction() | Starts a transaction |
| Commit | commit() | commit() | Saves transaction changes |
| Rollback | rollback() | rollBack() | Cancels uncommitted changes |
| Check active transaction | No direct equivalent in the same form | inTransaction() | Checks whether a transaction is active |
Common Database Errors in PHP
| Error | Meaning | How to Handle It |
|---|---|---|
| Connection error | PHP cannot connect to the database | Check host, username, password and database |
| Duplicate entry | A value violates a UNIQUE constraint | Check existing records |
| Foreign key constraint error | A related record does not satisfy a foreign key rule | Verify related records |
| Syntax error | SQL query contains invalid syntax | Review the SQL statement |
| Table not found | The requested table does not exist | Check the database and table name |
| Insufficient balance | Requested withdrawal exceeds available balance | Validate balance before updating |
Best Practices for Database Transactions and Error Handling
- Use InnoDB tables when transactions are required in MySQL.
- Start a transaction before performing related database operations.
- Use COMMIT only after all required operations succeed.
- Use ROLLBACK when an operation fails.
- Use prepared statements for queries containing supplied values.
- Validate amounts and account identifiers before processing transactions.
- Use
try-catchto handle database exceptions. - Log technical errors privately instead of showing them to website visitors.
- Keep transactions as short as practical.
- Use row locking when concurrent operations could cause incorrect results.
- Avoid performing unrelated tasks while a database transaction is open.
- Remember that DDL statements such as
CREATE TABLEandALTER TABLEcan cause implicit commits in MySQL, so they should not be treated like ordinary transactional INSERT, UPDATE or DELETE statements.
Key Takeaways
- A database transaction groups multiple database operations into one logical unit.
- COMMIT saves the transaction changes.
- ROLLBACK cancels uncommitted changes.
- SAVEPOINT creates a checkpoint inside a transaction.
- ACID stands for Atomicity, Consistency, Isolation and Durability.
- MySQLi and PDO both support database transactions.
- PDO uses
beginTransaction(),commit()androllBack(). - MySQLi uses
begin_transaction(),commit()androllback(). try-catchhelps handle database exceptions.error_log()records technical errors privately.- InnoDB supports transactions, unlike non-transactional storage engines such as MyISAM.
- Transactions help maintain consistent database records when multiple operations depend on one another.
Frequently Asked Questions (FAQs)
1. What is a database transaction in PHP?
A database transaction is a group of related database operations treated as one logical unit. The changes are saved together using COMMIT or cancelled using ROLLBACK.
2. What is the difference between COMMIT and ROLLBACK?
COMMIT permanently saves the changes made during a transaction. ROLLBACK cancels changes that have not yet been committed.
3. What are ACID properties in database transactions?
ACID represents Atomicity, Consistency, Isolation and Durability. These properties help ensure that database transactions are reliable and maintain data integrity.
4. How do we start a transaction in MySQLi?
Use the begin_transaction() method to start a transaction in MySQLi.
5. How do we start a transaction in PDO?
Use the beginTransaction() method to start a transaction in PDO.
6. What happens if an error occurs during a transaction?
If an error occurs before COMMIT, the application can execute ROLLBACK to cancel the uncommitted database changes.
7. What is a SAVEPOINT in MySQL?
A SAVEPOINT creates a checkpoint inside an active transaction. It allows the application to roll back selected changes without cancelling the entire transaction.
8. What is the purpose of try-catch in PHP database programming?
try-catch allows PHP to handle exceptions raised during database operations. It helps applications respond to errors without exposing technical details to visitors.
9. Does MySQLi support database transactions?
Yes. MySQLi supports transactions through methods such as begin_transaction(), commit() and rollback() when the storage engine supports transactions.
10. Does PDO support database transactions?
Yes. PDO supports transactions through beginTransaction(), commit() and rollBack().
11. Which MySQL storage engine supports transactions?
InnoDB supports transactions, row-level locking and rollback. MyISAM does not provide normal transaction support.
12. Why are database transactions important in banking applications?
Transactions help ensure that related operations, such as deducting money from one account and crediting another, are completed together. This prevents partial updates from leaving account balances inconsistent.
Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.
