SQL Dialects & Portability

Transaction and DDL Differences

Transaction labels do not guarantee identical anomalies, lock behavior, or failure handling, and DDL ranges from transactional to implicitly committed. Specify observable invariants, map them to each engine/version, retry complete units, and treat every migration as a dialect-specific operational program.

Isolation names are not equivalence proofs.
Savepoints limit rollback, not isolation.
Deadlocks and serialization failures are expected outcomes.
Locks differ in granularity and lifecycle.
DDL atomicity is a migration property.
Deferred constraints are not uniformly available.

Transaction and migration comparison
Dialect	Documented default isolation	DDL transaction boundary	Migration consequence
PostgreSQL	READ COMMITTED	most catalog DDL rolls back; commands such as CREATE DATABASE and concurrent index operations have restrictions	group compatible steps; isolate nontransactional commands
MySQL/InnoDB	REPEATABLE READ	many DDL statements implicitly commit; atomic DDL is crash consistency, not user rollback	checkpoint and compensate stepwise; inspect algorithm/lock
SQLite	SERIALIZABLE by serializing writes (with documented exceptions/modes)	schema changes participate in transactions; ALTER surface is deliberately limited	use documented table-rebuild procedure and test file/version
SQL Server	READ COMMITTED	many DDL statements can roll back in an explicit transaction, with documented exceptions and lock/log costs	test transactionability, blocking, and recovery per statement
Oracle	READ COMMITTED	implicit commit before and after DDL	assume each DDL is a committed boundary; design restartable stages

Begin a deliberate transaction

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- read invariant, write guarded state
COMMIT; -- retry whole transaction on SQLSTATE 40001 or deadlock 40P01

Migration atomicity checklist

For every step and supported version record:
- implicit commit / may-run-in-transaction / must-run-alone
- metadata and data locks; reader/writer blocking
- rewrite, log, temporary-space, replica and backup impact
- online/instant/concurrent prerequisites and fallback
- failure state and idempotent resume/compensation
- constraint-validation timing and deferred support
- rehearsal duration on production-shaped data

Never wrap a script in BEGIN and infer that it became atomic.