Available in versions: Dev (3.20) | Latest (3.19) | 3.18

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 TRANSACTION statement. This behaviour isn't standardised by jOOQ, it is recommended to always explicitly use START TRANSACTION.

Dialect support

This example using jOOQ:

startTransaction()

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, SQLServer, SQLite, Teradata

BEGIN TRANSACTION

Hana

DO BEGIN
END;

HSQLDB

START TRANSACTION READ WRITE

Informix

BEGIN WORK

Oracle

BEGIN
  NULL;
END;

ASE, Access, Aurora MySQL, Derby, DuckDB, Redshift, Snowflake, Sybase, Trino, Vertica

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo