Transaction management in PHP - Time & Space Complexity

Time Complexity: Transaction management

O(n)

Understanding Time Complexity

When managing transactions in PHP, it's important to know how the time to complete operations changes as the number of database actions grows.

We want to understand how the time cost increases when handling multiple queries inside a transaction.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

    $pdo->beginTransaction();
    $stmt = $pdo->prepare('INSERT INTO orders (product_id, quantity) VALUES (?, ?)');
    foreach ($items as $item) {
        $stmt->execute([$item['id'], $item['qty']]);
    }
    $pdo->commit();
    

This code starts a transaction, inserts multiple order items one by one, then commits the transaction.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Executing the prepared SQL insert inside the loop.
• How many times: Once for each item in the input array.

How Execution Grows With Input

Each additional item adds one more database insert operation inside the transaction.

Input Size (n)	Approx. Operations
10	10 insert executions
100	100 insert executions
1000	1000 insert executions

Pattern observation: The total operations grow directly in proportion to the number of items.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the transaction grows linearly with the number of items processed.

Common Mistake

[X] Wrong: "Starting a transaction makes the whole process constant time regardless of item count."

[OK] Correct: The transaction groups operations but each insert still runs separately, so time grows with item count.

Interview Connect

Understanding how transaction time grows helps you explain database efficiency and write better code that scales well.

Self-Check

"What if we batch all inserts into a single query inside the transaction? How would the time complexity change?"

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