Transaction management in PHP - Step-by-Step Execution

Concept Flow - Transaction management

Start Transaction

↓

Execute Queries

↓

Check for Errors?

Yes→Rollback Transaction

↓

End Transaction

↓

Commit Transaction

↓

End

This flow shows starting a transaction, running queries, checking for errors, and either committing or rolling back.

Execution Sample

PHP

<?php
$conn->beginTransaction();
$conn->exec("INSERT INTO users(name) VALUES('Alice')");
if ($conn->errorCode() != '00000') {
  $conn->rollBack();
} else {
  $conn->commit();
}
?>

This PHP code starts a transaction, inserts a user, and commits or rolls back based on error.

Execution Table

Step	Action	Transaction State	Query Executed	Error Detected	Result
1	Begin transaction	Active	None	No	Transaction started
2	Execute INSERT query	Active	INSERT INTO users(name) VALUES('Alice')	No	Query successful
3	Check for errors	Active	None	No	No errors found
4	Commit transaction	Committed	None	No	Transaction committed
5	End	Committed	None	No	Transaction complete

💡 Transaction ends after commit because no errors were detected.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
Transaction State	None	Active	Active	Active	Committed	Committed
Error Detected	False	False	False	False	False	False

Key Moments - 3 Insights

Why do we need to start a transaction before executing queries?

Starting a transaction groups queries so they can be committed or rolled back together, as shown in Step 1 of the execution_table.

What happens if an error is detected after executing queries?

If an error occurs, the transaction is rolled back to undo all changes, but in this example no error occurs (Step 3).

Why do we commit the transaction only after checking for errors?

Committing finalizes changes only if all queries succeed, ensuring data integrity, as shown in Step 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the transaction state after Step 2?

A Active

B Committed

C Rolled back

D None

💡 Hint

Check the 'Transaction State' column in the execution_table at Step 2.

At which step does the transaction get committed?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look for the 'Commit transaction' action in the execution_table.

If an error was detected at Step 3, what would happen next?

A Commit transaction

B Rollback transaction

C Execute another query

D End without commit or rollback

💡 Hint

Refer to the concept_flow where error detection leads to rollback.

Concept Snapshot

Transaction management in PHP:
- Use beginTransaction() to start.
- Execute queries inside transaction.
- Check for errors.
- Use commit() if no errors.
- Use rollBack() if errors occur.
Ensures all-or-nothing data changes.

Full Transcript

This example shows how PHP manages database transactions. First, beginTransaction() starts a transaction. Then, queries run inside it. After queries, the code checks for errors. If no errors, commit() saves changes permanently. If errors occur, rollBack() undoes all changes. This process ensures data stays correct and consistent.

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