Transaction management in PHP

Introduction

Transaction management helps keep your data safe and correct when you make multiple changes at once.

When you need to save several related changes to a database together.

When you want to make sure either all changes happen or none at all.

When updating money or important records that must not be lost or broken.

When you want to avoid errors causing partial updates in your data.

Syntax

PHP

<?php
$pdo->beginTransaction();
// run queries
$pdo->commit();
// or if error
$pdo->rollBack();
?>

Use beginTransaction() to start a transaction.

Use commit() to save all changes if everything is fine.

Examples

Starts a transaction, inserts a user, then saves the change.

PHP

<?php
$pdo->beginTransaction();
$pdo->exec("INSERT INTO users (name) VALUES ('Alice')");
$pdo->commit();
?>

Transfers money between accounts safely. If any update fails, it cancels all changes.

PHP

<?php
$pdo->beginTransaction();
try {
  $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
  $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
  $pdo->commit();
} catch (Exception $e) {
  $pdo->rollBack();
}
?>

Sample Program

This program creates two accounts, then safely transfers 200 from Alice to Bob using a transaction. If anything goes wrong, it cancels the transfer.

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// Create table
$pdo->exec("CREATE TABLE accounts (id INTEGER PRIMARY KEY, name TEXT, balance INTEGER)");

// Insert initial data
$pdo->exec("INSERT INTO accounts (name, balance) VALUES ('Alice', 500), ('Bob', 300)");

try {
    $pdo->beginTransaction();

    // Alice sends 200 to Bob
    $pdo->exec("UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice'");
    $pdo->exec("UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob'");

    $pdo->commit();
    echo "Transaction completed successfully.\n";
} catch (Exception $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage() . "\n";
}

// Show final balances
$stmt = $pdo->query("SELECT name, balance FROM accounts");
foreach ($stmt as $row) {
    echo $row['name'] . ': ' . $row['balance'] . "\n";
}

Important Notes

Always set error mode to PDO::ERRMODE_EXCEPTION to catch errors easily.

Use rollBack() to undo changes if something goes wrong.

Transactions work only if your database supports them (like MySQL InnoDB, PostgreSQL, SQLite).

Summary

Transaction management groups multiple database changes into one safe action.

Use beginTransaction(), commit(), and rollBack() to control transactions.

This helps keep your data correct and avoids partial updates.

Practice

1. What is the main purpose of using beginTransaction() in PHP when working with databases?

easy

A. To start a group of database operations that can be committed or rolled back together

B. To immediately save changes to the database

C. To close the database connection

D. To check if the database is available

Solution

1. Step 1: Understand the role of beginTransaction()

   This function starts a transaction, grouping multiple database changes into one unit.

2. Step 2: Recognize transaction benefits

   It allows committing all changes at once or rolling back if something goes wrong, ensuring data integrity.

3. Final Answer:

   To start a group of database operations that can be committed or rolled back together -> Option A

4. Quick Check:

   beginTransaction() starts a transaction [OK]

Hint: beginTransaction() starts safe grouped DB changes [OK]

Common Mistakes:

• Thinking it saves changes immediately
• Confusing it with closing the connection
• Assuming it checks database availability

2. Which of the following is the correct syntax to commit a transaction in PHP PDO?

easy

A. $pdo->commitTransaction();

B. $pdo->transactionCommit();

C. commit($pdo);

D. $pdo->commit();

Solution

1. Step 1: Recall PDO commit method

   The correct method to save all changes in a transaction is commit() called on the PDO object.

2. Step 2: Check method names

   Only $pdo->commit(); matches the correct PDO syntax; others are invalid method names.

3. Final Answer:

   $pdo->commit(); -> Option D

4. Quick Check:

   commit() saves transaction changes [OK]

Hint: Use $pdo->commit() to save transaction changes [OK]

Common Mistakes:

• Using incorrect method names like commitTransaction()
• Trying to call commit as a function with argument
• Confusing commit with rollback

3. What will be the output of the following PHP code snippet?

$pdo->beginTransaction();
$pdo->exec("INSERT INTO users (name) VALUES ('Alice')");
$pdo->rollBack();
echo $pdo->query("SELECT COUNT(*) FROM users")->fetchColumn();

medium

A. 0

B. 1

C. SyntaxError

D. Throws Exception

Solution

1. Step 1: Analyze transaction commands

   The code starts a transaction, inserts a user, then rolls back, undoing the insert.

2. Step 2: Check final data count

   Since the insert was rolled back, the users table remains unchanged, so count is 0.

3. Final Answer:

   0 -> Option A

4. Quick Check:

   rollBack() undoes insert, count = 0 [OK]

Hint: rollBack() cancels changes, so count stays zero [OK]

Common Mistakes:

• Assuming insert stays after rollBack()
• Confusing commit() with rollBack()
• Expecting syntax or runtime error

4. Identify the error in this PHP transaction code:

try {
  $pdo->beginTransaction();
  $pdo->exec("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
  $pdo->exec("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
  $pdo->commit();
} catch (Exception $e) {
  $pdo->commit();
}

medium

A. Missing beginTransaction() call

B. Using commit() in catch block instead of rollBack()

C. No exception handling block

D. Using exec() instead of query()

Solution

1. Step 1: Review transaction error handling

   In case of error, changes should be undone with rollBack(), not committed.

2. Step 2: Identify catch block mistake

   The catch block incorrectly calls commit(), which saves partial or faulty changes.

3. Final Answer:

   Using commit() in catch block instead of rollBack() -> Option B

4. Quick Check:

   Errors need rollBack(), not commit() [OK]

Hint: Use rollBack() on error, not commit() [OK]

Common Mistakes:

• Calling commit() in catch block
• Forgetting to handle exceptions
• Confusing exec() and query() methods

5. You want to transfer money between two accounts safely using PHP transactions. Which of the following sequences of steps is correct?

hard

A. Begin transaction, update sender balance, commit transaction, update receiver balance

B. Update sender balance, update receiver balance, begin transaction, commit transaction

C. Begin transaction, update sender balance, update receiver balance, commit transaction

D. Update sender balance, begin transaction, update receiver balance, commit transaction

Solution

1. Step 1: Understand transaction grouping

   All related updates must be inside the transaction to ensure atomicity.

2. Step 2: Check step order

   Starting with beginTransaction(), then updating both accounts, and finally committing ensures either all changes apply or none.

3. Final Answer:

   Begin transaction, update sender balance, update receiver balance, commit transaction -> Option C

4. Quick Check:

   All DB changes inside transaction before commit [OK]

Hint: Start transaction before updates, commit after all done [OK]

Common Mistakes:

• Updating before starting transaction
• Committing too early before all updates
• Not grouping all changes inside transaction