Does snapshot isolation prevent every concurrency anomaly?

No. Write skew and other serialization anomalies can remain.

Can a transaction see its own updates?

Yes, even though its view of other committed work follows the snapshot rules.

Is Repeatable Read identical in every database?

No. Implementations and guarantees differ, so the product documentation matters.

AI Glossary / Data engineering and architecture / Advanced

Snapshot isolation

Definition

Snapshot isolation is a transaction-isolation approach in which a transaction reads from a consistent snapshot of committed data, together with its own changes, while concurrent transactions may continue. Conflicting writes are controlled, but ordinary snapshot isolation does not guarantee that every committed outcome is equivalent to executing transactions one at a time. In particular, transactions updating different records can still create a write-skew anomaly.

What it means

A transaction needs a coherent view while it performs several operations. Without a stable view, later queries might encounter changes committed by other work in the meantime. Snapshot isolation provides stability for the transaction's view, but stability is not the same as protection of every rule spanning several records. Two transactions can each make a reasonable decision from their snapshots and jointly produce an unacceptable outcome. For applications using AI-generated actions, this remains a database concern: a plausible decision based on a snapshot does not establish that concurrent decisions can safely be committed together.

How it works

A database commonly uses multiple versions of data so reads can select versions visible to the transaction's snapshot. PostgreSQL documents its Repeatable Read implementation as snapshot isolation and distinguishes it from its Serializable level. In that implementation, the snapshot is established by the first non-transaction-control statement, and concurrent-update conflicts can require the whole transaction to be retried. Serializable adds protection against serialization anomalies. Product names and details differ, so the isolation label alone is not enough to infer behavior across systems. The key distinction is between a stable view and guarantees about the combined effects of concurrent transactions.

Three practical examples

A reporting transaction performs several queries against a stable database view while other users continue changing records.
An application checks a rule involving several rows and tests whether two concurrent updates to different rows can violate that rule.
A development team verifies its retry behavior when the database rejects an update because another transaction changed the same record.

Think of it this way

Imagine two planners each receiving the same photograph of a noticeboard. Each can study a stable picture while the real board changes. But if both independently decide to remove different notices, their stable pictures do not guarantee that the final board satisfies a rule requiring at least one notice to remain. The analogy illustrates the limitation; the database's actual conflict and visibility rules are more precise than a photograph.

Worked example

Consider an original simplified system with two processing routes, A and B, both initially enabled. A business rule requires at least one route to remain enabled. Transaction T1 reads a snapshot showing A = 1 and B = 1, then disables A because B appears available. Concurrent transaction T2 reads the same starting state and disables B because A appears available. They update different rows. Under ordinary snapshot isolation, without additional protection for the cross-row rule, both can commit, leaving A + B = 0. If the transactions ran one at a time with the same checks, the second would see only one enabled route and refuse to disable it. The discrepancy demonstrates write skew, not a dirty read of uncommitted data.

Illustrative case study

In a fictional data-processing platform, an administrative tool permits a route to be disabled when another remains enabled. Its single-user tests pass. A concurrency test sends two requests at once, each targeting a different route, and exposes the combined-rule problem described above. Engineers review the isolation level and redesign the protection for the invariant, testing both concurrent success and retry paths. They also ensure that an AI assistant using the tool does not treat a rejected transaction as a completed change. The case illustrates why business-rule tests need concurrent scenarios. It does not prescribe one database setting as a universal solution.

Common misconceptions and mistakes

Snapshot isolation is not synonymous with serializability. Nor is it simply a database backup or a permanently frozen copy. A transaction can see its own writes while its view of other transactions remains tied to the relevant snapshot. Another mistake is assuming that no two transactions writing the same row means no logical conflict exists. Cross-row rules can create dependencies even when write sets are disjoint. Finally, an application should not retry only the last failed statement when its database requires restarting the transaction; earlier decisions may need to be made again against a new view.

Business use

The concept helps teams align database behavior with application rules. Identify invariants that span records, such as requiring an available processing route, rather than testing only individual row updates. Ask what the selected isolation level prevents and which conflict outcomes the application must handle. For AI-enabled operations, verify that tool responses distinguish committed changes from aborted attempts and that retries repeat the necessary checks. A stable analytical view can be valuable, but it does not by itself make all decisions based on that view safe under concurrent modification. Evaluate the full transaction design against the intended rule.

Check your understanding

What did both toy transactions initially see?

Answer: Two enabled routes.

Why did neither overwrite the other's row?

Answer: Each disabled a different route.

What final total violated the rule?

Answer: Zero enabled routes.

Why could the same result not arise from the stated checks in a serial execution?

Answer: The second transaction would observe that only one route remained and decline the change.

Related terms

Multiversion concurrency control
transaction isolation
repeatable read
write skew
serializability
transaction retry

Learn next

Write a fictional rule involving two records and design two transactions that each preserve it when run alone. Trace their reads and writes under overlapping snapshots. Determine whether disjoint writes can still break the rule. Then identify what guarantee or coordination the implementation would need, and include the application's behavior when a transaction must be retried.

Sources

PostgreSQL documentation, Transaction Isolation: https://www.postgresql.org/docs/current/transaction-iso.html PostgreSQL project wiki, Serializable Snapshot Isolation: https://wiki.postgresql.org/wiki/SSI The processing-route calculation, noticeboard analogy and platform case are original educational illustrations. PostgreSQL provides the concrete implementation example; the entry does not generalize its isolation-level names or exact snapshot timing to every database product.

Previous: SMOTE Next: Sobel edge detector