ACID properties in MySQL - Time & Space Complexity

Time Complexity: ACID properties

O(1)

Understanding Time Complexity

We want to understand how the time cost of database transactions changes as the amount of data grows.

Specifically, how the ACID properties affect the work done during transactions.

Scenario Under Consideration

Analyze the time complexity of a transaction ensuring ACID properties.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

This code transfers money between two accounts within a transaction that must be atomic, consistent, isolated, and durable.

Identify Repeating Operations

Look for repeated actions that affect time cost.

• Primary operation: Two UPDATE statements on specific rows.
• How many times: Each UPDATE runs once per transaction regardless of data size.

How Execution Grows With Input

The transaction updates fixed rows, so the number of operations stays the same even if the table grows.

Input Size (n)	Approx. Operations
10 rows	2 updates
1000 rows	2 updates
1,000,000 rows	2 updates

Pattern observation: The work does not increase with table size because only specific rows are updated.

Final Time Complexity

Time Complexity: O(1)

This means the time to complete the transaction stays about the same no matter how big the table is.

Common Mistake

[X] Wrong: "Because the table is large, the transaction will take longer every time."

[OK] Correct: The transaction only changes a fixed number of rows, so the size of the whole table does not affect the time much.

Interview Connect

Understanding how ACID transactions scale helps you explain database behavior clearly and confidently in real-world situations.

Self-Check

"What if the transaction updated every row in the table? How would the time complexity change?"