Transaction Isolation

Transaction Isolation

TMI runs every write transaction at SERIALIZABLE isolation on both of its supported databases — PostgreSQL (development) and Oracle Autonomous Database (production). This page explains why, how it is enforced, the important semantic difference between the two databases, and how to verify it.

Why serializable

The database default on both PostgreSQL and Oracle is READ COMMITTED, which permits subtle, non-deterministic anomalies — lost updates and read/write skew — that surface as rare, hard-to-reproduce data-integrity bugs. Some of those anomalies have a security dimension: write skew can defeat an authorization invariant that is enforced across more than one row.

TMI's posture is to start from the safe default (serializable) and tune performance afterward, rather than ship weak isolation and discover the anomalies in production.

How it is enforced

There are two layers.

1. Per-transaction (authoritative, cross-database)

The retry wrapper auth/db/retry.go::WithRetryableGormTransaction threads &sql.TxOptions{Isolation: sql.LevelSerializable} into every transaction it opens. This is the primary, leak-proof mechanism and the only lever that exists on Oracle:

• GORM → pgx emits BEGIN ISOLATION LEVEL SERIALIZABLE.
• GORM → godror emits ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE, cached per pooled connection.

The wrapper also classifies serialization failures by error code (not string matching) and retries the whole transaction closure with bounded, jittered backoff:

• PostgreSQL: SQLSTATE 40001 (serialization failure), 40P01 (deadlock).
• Oracle: ORA-08177 (can't serialize access), ORA-00060 (deadlock), ORA-00054 (resource busy).

Because the closure can run more than once, it must be idempotent — keep non-idempotent side effects (outbound calls, one-time-token consumption) out of the transaction or guard them so a replay is harmless.

⚠️ Never request RepeatableRead — godror rejects it. And never mix nil-option transactions with serializable ones on the same pooled Oracle connection: godror caches the level per physical connection, so a later nil transaction reusing it would silently run serializable.

2. PostgreSQL role default (defense-in-depth)

On PostgreSQL only, the server pins the connecting role's default at startup:

ALTER ROLE CURRENT_USER SET default_transaction_isolation = 'serializable';

This is a backstop: any connection that somehow bypasses the wrapper still begins its transactions at SERIALIZABLE instead of READ COMMITTED. It is installed by dbschema.InstallPostgresDefaultIsolation (invoked from the server's post-migration hook) and is non-fatal — if it fails, the per-transaction wrapper still enforces the level.

ALTER ROLE … SET affects sessions opened after it runs; connections already in the pool keep their session default until they are recycled or the server restarts. Because this layer is only a backstop, that lag is acceptable.

ALTER ROLE CURRENT_USER is used rather than ALTER DATABASE because it is the portable form — it works on managed PostgreSQL (e.g. Heroku), where the connecting role owns its own settings but may not own the database.

Oracle has no equivalent. There is no ALTER DATABASE/ALTER ROLE default-isolation knob, and ADB blocks the logon-trigger workaround. On Oracle, enforcement is per-transaction (layer 1) only.

PostgreSQL ≠ Oracle: the semantic gap

SERIALIZABLE does not mean the same thing on both databases:

	PostgreSQL	Oracle
Mechanism	Serializable Snapshot Isolation (SSI)	Snapshot isolation (SI)
Tracks read/write dependencies	Yes	No
Detects write skew	Yes — aborts with 40001	No
Aborts when	A dependency cycle could break serializability	Only when modifying a row another tx committed since the snapshot (ORA-08177)

Oracle serializable is meaningfully weaker. It will not catch write-skew anomalies. Therefore, cross-row invariants must also be protected with explicit SELECT … FOR UPDATE row locks or unique constraints — isolation alone is not sufficient on Oracle.

Practical consequences

• Keep write transactions short. This reduces both ORA-08177 and ORA-01555 ("snapshot too old") on Oracle, and reduces abort/retry churn on both databases.
• Hot single rows are an anti-pattern under serializable. A counter row updated on every write serializes all writers and, on Oracle, converts lock contention into ORA-08177 abort churn. TMI's global ThreatModel alias was moved off such a row onto a non-transactional database sequence for exactly this reason (see issue #452).
• Long read-only/reporting paths may stay on READ COMMITTED — they do not need serializable and would only add retry overhead.
• Don't run AutoMigrate DDL concurrently with a serializable workload.

Verifying

PostgreSQL

A freshly-opened connection (one that does not go through the wrapper) should inherit the role default:

SELECT current_setting('default_transaction_isolation');
-- expected: serializable

This is exercised in CI by TestInstallPostgresDefaultIsolation_Integration.

Oracle ADB

There is no role default to read; verification confirms the per-transaction mechanism instead — that wrapper transactions issue ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE and that ORA-08177 is observed and retried under contention. Run the Oracle integration suite against a real ADB connection (make test-integration-oci).

See Also

• Database-Operations -- PostgreSQL and Redis operational commands
• Database-Security-Strategies -- Least-privilege database users
• Performance-and-Scaling -- Throughput tuning and contention