|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 START TRANSACTION -- DB2 BEGIN END -- EXASOL COMMIT -- FIREBIRD EXECUTE BLOCK AS BEGIN END -- H2, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, TERADATA BEGIN TRANSACTION -- HANA DO BEGIN END; -- HSQLDB START TRANSACTION READ WRITE -- INFORMIX BEGIN WORK -- ORACLE BEGIN NULL; END; -- ACCESS, ASE, AURORA_MYSQL, DERBY, DUCKDB, REDSHIFT, SNOWFLAKE, SYBASE, TRINO, VERTICA /* UNSUPPORTED */