|previous : next
START TRANSACTION statement
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
In standard SQL, a
START TRANSACTION statement can be issued in order to switch from a non-transactional session state to a transactional one. The standard mandates that an error should be raised if the session was already in a transaction. Implementations usually have a different opinion, including:
- Follow the SQL standard.
- Ignore the statement if a transaction exists.
- Support the statement only at the top level, not within a procedural context.
- Do not support the statement at all, always starting transactions implicitly.
jOOQ attempts to standardise on this behaviour in a way that it should be safe to always start a transaction, even if the statement is not supported, in case of which a no-op is generated.
Depending on whether this statement is supported, dialects might treat COMMIT and ROLLBACK as statements effectively ending a transaction, after which transactional code can only be executed after another explicit
START TRANSACTIONstatement. This behaviour isn't standardised by jOOQ, it is recommended to always explicitly use
This example using jOOQ:
Translates to the following dialect specific expressions:
Aurora Postgres, BigQuery, CockroachDB, MariaDB, MemSQL, MySQL, Postgres, YugabyteDB
EXECUTE BLOCK AS BEGIN END
H2, SQLDataWarehouse, SQLServer, SQLite, Teradata
DO BEGIN END;
START TRANSACTION READ WRITE
BEGIN NULL; END;
ASE, Access, Aurora MySQL, Derby, DuckDB, Redshift, Snowflake, Sybase, Trino, Vertica
/* UNSUPPORTED */