Data consistency & transactions

I separate atomicity from isolation. A transaction makes its changes commit or roll back together, but its reads can still become stale at common isolation levels. Two successful transactions can therefore violate a business rule. For stock, I prefer a conditional decrement and check the affected-row count. If several rows define the rule, I consider Serializable isolation or a shared lock boundary. I choose protection from the invariant rather than assuming BEGIN TRANSACTION solves concurrency.

I describe them with two transactions. A dirty read observes another transaction's uncommitted change, which may later roll back. A non-repeatable read sees a different committed value when reading the same row again. A phantom means repeating a predicate query produces a different set of rows, such as an extra pending order. These distinctions matter because protecting existing rows is not necessarily enough to protect the range of rows matching a condition.

I would not add NOLOCK just to make a report faster. It permits dirty reads and can return missing or duplicated rows, so the result may never have represented a committed database state. It also does not remove all locking, including schema locking. I investigate the execution plan, indexes and transaction duration first. If reader-writer blocking is the issue, I assess row-versioned reads with their storage and semantic trade-offs rather than silently accepting incorrect results.

A lost update happens when two callers read the same value, compute replacements separately, and the later write overwrites the earlier result. If stock starts at ten, two callers may both write nine after selling one item each. I avoid this with an atomic database expression, a concurrency-token comparison, or a lock held across the decision. I also check business conditions such as sufficient stock, because an atomic decrement alone can still produce a negative quantity.

I expect Read Committed in both engines, but I check how it is implemented and the actual database configuration. PostgreSQL uses a statement snapshot for Read Committed. Traditional SQL Server defaults to locking reads unless READ_COMMITTED_SNAPSHOT is enabled; Azure SQL Database enables that option by default. PostgreSQL also treats Read Uncommitted as Read Committed. Those differences affect blocking and repeat reads, so I do not transfer assumptions between engines just because an isolation-level name matches.

In SQL Server, RCSI changes Read Committed reads to use a committed snapshot for each statement. Two statements in one transaction can therefore see different committed data. SNAPSHOT isolation uses a transaction snapshot established at first data access, while still showing the transaction's own writes. It can raise update conflicts and requires separate enablement. I use the distinction when several reads must agree, and I still analyze multi-row invariants because snapshot isolation is not equivalent to Serializable.

I configure a concurrency token and preserve the version originally shown to the user. EF includes that original token in the update or delete predicate. If no row matches, SaveChanges raises DbUpdateConcurrencyException. I then apply an explicit policy: reload, show the conflict, or merge fields after reviewing database values. I do not blindly replace the original version and retry, because that can discard someone else's edit. SQL Server rowversion is a binary token, not a timestamp value.

I prefer optimistic concurrency when conflicts are uncommon or an edit spans user interaction, since holding a database lock would be expensive. For a short decision under frequent contention, I may use SELECT FOR UPDATE in PostgreSQL or appropriate SQL Server update locks. The protected read and write must share one transaction and connection. I keep the lock scope short and plan for deadlocks. For a single conditional counter change, one atomic UPDATE is often simpler than either approach.

I validate that the requested quantity is positive and issue a conditional UPDATE that subtracts it only where the product has enough stock. I treat one affected row as a successful reservation and zero as failure. If creating the order must be atomic with reservation, both operations share a transaction. This works across application instances because the database coordinates the write. I also add suitable constraints and a reservation identity if retries must not reserve the same order twice.

Blocking means one transaction waits for another to release a conflicting resource. A deadlock is a cycle of dependencies, so waiting alone cannot resolve it. The database aborts a victim. I inspect the deadlock graph, use a consistent access order, shorten transactions and improve indexes where scans widen locking. I retry the victim's entire transaction with fresh reads and bounded backoff, making sure replay cannot repeat an unprotected external side effect such as charging a card.

In SQL Server, escalation can replace many fine-grained locks with a broader table or partition lock, reducing lock-management overhead while potentially blocking unrelated work. The exact scope depends on configuration and workload. I investigate large scans, transaction size and indexing rather than relying on ROWLOCK as a guarantee. I also avoid generalizing this mechanism to PostgreSQL, which has a different locking implementation and does not follow SQL Server's escalation policy.

Snapshot isolation gives a consistent view but does not necessarily protect every predicate used in a decision. Two doctors can each observe that two people are on call, then update different rows to leave. Neither overwrites the other, yet nobody remains. A rowversion on each doctor does not fix that. I use Serializable with whole-transaction retries or serialize the decision through a shared roster row locked before checking the count. The protected boundary must match the business invariant.

I first define the scope of the guarantee, usually a user session. The straightforward solution is to route that session's relevant reads to the primary. Another option is a commit-position token and a replica that waits until it has applied at least that position before serving the read. I would not call a fixed two-second sticky window a guarantee unless replication lag has a proven bound. I also make sure caches do not reintroduce older results.

I think about the ordering each model promises. Linearizability makes each operation appear instantaneous and respects real-time completion across clients. Sequential consistency provides one order preserving each client's program order, but need not respect real time between clients. Causal consistency preserves cause-and-effect relationships while allowing independent operations to be observed in different orders. I choose the weakest guarantee that still supports the business rule, then check the latency, availability and session requirements instead of treating consistency as one switch.

Two-phase commit coordinates an atomic decision across supported participants, but prepared resources may remain held while the decision is unavailable. It also couples availability and requires infrastructure support many external APIs lack. A saga instead commits local steps and performs compensating business actions when later steps fail. That supports independent services, but intermediate states become visible and compensation can fail. I prefer a local transaction when the boundary permits it; a saga is a deliberate consistency trade-off, not a free replacement for ACID.

I describe the guarantee precisely. An outbox atomically records the business change and publication intent, but the dispatcher can publish twice after a crash. An inbox deduplicates messages for a consumer when its unique marker and local effect commit together. That gives effectively-once local effects under the stated assumptions, not universal exactly-once execution. If processing calls a payment provider, the inbox alone is insufficient; I need provider-side idempotency and reconciliation for ambiguous outcomes.

A reader may fetch the old database value before a writer commits. The writer then commits and invalidates the cache, after which the reader populates that cache with its old value. TTL limits retention but does not prevent the race. Depending on the requirement, I use version-aware writes, coordination or authoritative reads for critical decisions. An outbox improves eventual delivery of invalidations, but it still leaves a delay window and does not make the cache strongly consistent.