Read phenomena (dirty reads, phantom reads) in SQL - Time & Space Complexity

Time Complexity: Read phenomena (dirty reads, phantom reads)

O(n)

Understanding Time Complexity

When databases read data during transactions, the way they handle changes affects how long queries take.

We want to understand how reading data that might be changing impacts the work done by the database.

Scenario Under Consideration

Analyze the time complexity of this transaction reading data with possible dirty or phantom reads.

BEGIN TRANSACTION;
SELECT * FROM Orders WHERE CustomerID = 123;
-- Another transaction may insert or update Orders here
SELECT COUNT(*) FROM Orders WHERE CustomerID = 123;
COMMIT;
    

This code reads orders for a customer, but other changes might happen during the transaction causing dirty or phantom reads.

Identify Repeating Operations

Look for repeated work that affects time.

• Primary operation: Scanning or searching the Orders table for matching CustomerID rows.
• How many times: Twice in this example, once for the SELECT * and once for the COUNT(*).

How Execution Grows With Input

As the number of orders grows, the database must check more rows each time it reads.

Input Size (n)	Approx. Operations
10	About 20 row checks (2 reads x 10 rows)
100	About 200 row checks
1000	About 2000 row checks

Pattern observation: The work roughly doubles with the number of rows because the query runs twice, each scanning the data.

Final Time Complexity

Time Complexity: O(n)

This means the time to read grows directly with the number of rows matching the query.

Common Mistake

[X] Wrong: "Reading data during a transaction always takes the same time regardless of data changes."

[OK] Correct: If other transactions add or change rows, the database may do extra work to handle these reads, making the time grow with data size and changes.

Interview Connect

Understanding how reading data during transactions affects performance shows you know how databases handle real-world data changes.

Self-Check

"What if we added an index on CustomerID? How would that change the time complexity of these reads?"