Deleted Before Commit

An engineer moves the cache invalidation inside the database transaction so that "either both happen or neither does".

def set_price(sku: str, price: int) -> None:
    with db.transaction():                    # 1
        db.execute(
            "UPDATE products SET price = %s WHERE sku = %s", (price, sku)
        )                                     # 2
        cache.delete(f"price:{sku}")          # 3
    # COMMIT happens here, when the block exits   4

def get_price(sku: str) -> int:
    v = cache.get(f"price:{sku}")             # 5
    if v is None:
        v = db.query_one(
            "SELECT price FROM products WHERE sku = %s", (sku,)
        )                                     # 6
        cache.set(f"price:{sku}", v, ex=300)  # 7
    return v

A reader runs concurrently with one writer. What is the worst outcome this ordering allows, and how long does it last?

A

The reader sees the new price but a torn value, because line 7 is not covered by the transaction

B

A reader that misses at line 5 between lines 3 and 4 reads the pre-commit price at line 6 and writes it back at line 7, so the stale price survives for the full 300 second TTL or until the next write

C

The reader blocks at line 6 until the transaction commits, so it always caches the new price

D

Nothing, because the delete at line 3 rolls back with the transaction if the commit fails