Transaction management in PHP - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this PHP transaction code?

Consider this PHP code using PDO for transaction management. What will it output?

PHP

<?php
try {
  $pdo = new PDO('sqlite::memory:');
  $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  $pdo->exec('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER)');
  $pdo->exec('INSERT INTO accounts (balance) VALUES (100)');
  $pdo->beginTransaction();
  $pdo->exec('UPDATE accounts SET balance = balance - 50 WHERE id = 1');
  throw new Exception('Simulated error');
  $pdo->exec('UPDATE accounts SET balance = balance + 50 WHERE id = 1');
  $pdo->commit();
} catch (Exception $e) {
  $pdo->rollBack();
  echo 'Transaction failed: ' . $e->getMessage();
}
$stmt = $pdo->query('SELECT balance FROM accounts WHERE id = 1');
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo "\nBalance: " . $row['balance'];
?>

A

Transaction failed: Simulated error
Balance: 100

B

Transaction failed: Simulated error
Balance: 50

C Balance: 50

D Balance: 100

💡 Hint

Think about what happens when an exception is thrown inside a transaction.

The exception causes the transaction to roll back, so the balance remains unchanged at 100. The catch block prints the error message, then the balance is fetched and printed.

🧠 Conceptual

intermediate

Which statement best describes a transaction in PHP PDO?

Choose the best description of what a transaction does in PHP PDO.

A A transaction is used to speed up database queries by caching results.

B A transaction automatically commits each SQL statement separately.

C A transaction groups multiple database operations so they all succeed or fail together.

D A transaction is a PHP function that encrypts database data.

💡 Hint

Think about what happens if one operation in a group fails.

A transaction ensures that either all operations succeed or none do, keeping data consistent.

❓ Predict Output

advanced

What error does this PHP transaction code raise?

Look at this PHP code snippet. What error will it raise when run?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->beginTransaction();
$pdo->exec('CREATE TABLE test (id INTEGER)');
$pdo->commit();
$pdo->commit();
?>

A PDOException: There is no active transaction

B SyntaxError: Unexpected token commit

C No error, commits successfully twice

D Fatal error: Call to undefined method commit()

💡 Hint

What happens if you call commit twice without a new transaction?

Calling commit when no transaction is active causes a PDOException.

🔧 Debug

advanced

Why does this transaction not roll back on error?

Given this PHP code, why does the balance update even though an error occurs?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER)');
$pdo->exec('INSERT INTO accounts (balance) VALUES (100)');
$pdo->beginTransaction();
$pdo->exec('UPDATE accounts SET balance = balance - 50 WHERE id = 1');
try {
  throw new Exception('Error happened');
  $pdo->commit();
} catch (Exception $e) {
  echo 'Error: ' . $e->getMessage();
}
$stmt = $pdo->query('SELECT balance FROM accounts WHERE id = 1');
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo "\nBalance: " . $row['balance'];
?>

A Because exceptions do not affect transactions in PDO.

B Because rollBack() was never called, so the transaction was not undone.

C Because commit() was called before the exception.

D Because the database does not support transactions.

💡 Hint

Look for missing rollback calls in the catch block.

The code never calls rollBack() after the exception, so the transaction stays open and the update is committed implicitly.

🚀 Application

expert

How many rows are affected after this transaction code runs?

Given this PHP code, how many rows in the 'users' table will have age = 30 after it runs?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, age INTEGER)');
$pdo->exec('INSERT INTO users (age) VALUES (25), (28), (30), (22)');
$pdo->beginTransaction();
$pdo->exec('UPDATE users SET age = 30 WHERE age < 30');
$pdo->rollBack();
$pdo->exec('UPDATE users SET age = 30 WHERE age < 30');
?>

A 1

B 3

C 0

D 4

💡 Hint

Consider what rollBack() does and when the second update runs.

The first update is undone by rollBack(), so the ages remain 25, 28, 30, 22. The second update runs outside a transaction and changes 3 rows (ages 25, 28, 22) to 30, so now 4 rows have age = 30.

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