Transaction states in DBMS Theory - Time & Space Complexity

Time Complexity: Transaction states

O(n)

Understanding Time Complexity

When working with transactions in a database, it is important to understand how the time taken changes as the transaction moves through different states.

We want to know how the processing time grows as the transaction progresses from start to finish.

Scenario Under Consideration

Analyze the time complexity of the following transaction state changes.

BEGIN TRANSACTION;
-- Perform several SQL operations
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

This snippet shows a simple transaction that updates two accounts and then commits the changes.

Identify Repeating Operations

Look for repeated steps or operations that affect time.

• Primary operation: Executing SQL update statements inside the transaction.
• How many times: Each update runs once, but the number of operations depends on how many rows are affected.

How Execution Grows With Input

The time taken depends on how many rows each update affects and how complex the operations are.

Input Size (rows affected)	Approx. Operations
10	About 10 updates per statement
100	About 100 updates per statement
1000	About 1000 updates per statement

Pattern observation: As the number of rows affected grows, the time grows roughly in direct proportion.

Final Time Complexity

Time Complexity: O(n)

This means the time to complete the transaction grows linearly with the number of rows affected by the operations inside it.

Common Mistake

[X] Wrong: "The transaction time is always constant regardless of data size."

[OK] Correct: The time depends on how many rows the transaction modifies; more rows mean more work and longer time.

Interview Connect

Understanding how transaction time grows helps you explain database performance and reliability in real projects.

Self-Check

"What if the transaction included a loop that updated rows one by one instead of a single update statement? How would the time complexity change?"