COMMIT and ROLLBACK behavior in SQL - Time & Space Complexity

Time Complexity: COMMIT and ROLLBACK behavior

O(n)

Understanding Time Complexity

When working with transactions in databases, it is important to understand how the time to complete operations changes as the amount of data grows.

We want to know how the cost of committing or rolling back changes grows with the size of the transaction.

Scenario Under Consideration

Analyze the time complexity of the following SQL transaction commands.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
-- More updates here ...
COMMIT;
-- or
ROLLBACK;

This snippet shows a transaction that updates multiple rows and then either saves all changes with COMMIT or cancels them with ROLLBACK.

Identify Repeating Operations

Look at what repeats during the transaction.

• Primary operation: Updating rows inside the transaction.
• How many times: Once per row updated, which can be many times depending on data.

The COMMIT or ROLLBACK itself processes all these changes at once.

How Execution Grows With Input

As the number of updated rows grows, the work to commit or rollback also grows.

Input Size (rows updated)	Approx. Operations
10	Processes 10 changes
100	Processes 100 changes
1000	Processes 1000 changes

Pattern observation: The work grows roughly in direct proportion to the number of changes made.

Final Time Complexity

Time Complexity: O(n)

This means the time to commit or rollback grows linearly with the number of changes in the transaction.

Common Mistake

[X] Wrong: "COMMIT or ROLLBACK always takes the same time no matter how many rows changed."

[OK] Correct: The database must process each change to save or undo it, so more changes mean more work and longer time.

Interview Connect

Understanding how transaction commands scale helps you explain database behavior clearly and shows you grasp practical performance considerations.

Self-Check

"What if the transaction only updates a single row but includes complex triggers? How might that affect the time complexity?"