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.