SET TRANSACTION

IGNORE LIMBO
LOCK TIMEOUT
NO AUTO UNDO

Available in: DSQL, ESQL

Changed in: 2.0

Description: Starts and optionally configures a transaction.

Syntax:

SET TRANSACTION
   [NAME hostvar]
   [READ WRITE | READ ONLY]
   [ [ISOLATION LEVEL] { SNAPSHOT [TABLE STABILITY]
                         | READ COMMITTED [[NO] RECORD_VERSION] } ]
   [WAIT | NO WAIT]
   [LOCK TIMEOUT seconds]
   [NO AUTO UNDO]
   [IGNORE LIMBO]
   [RESERVING <tables> | USING <dbhandles>]

<tables>      ::=  <table_spec> [, <table_spec> ...]

<table_spec>  ::=  tablename [, tablename ...]
                      [FOR [SHARED | PROTECTED] {READ | WRITE}]

<dbhandles>   ::=  dbhandle [, dbhandle ...]
The NAME option is only available in ESQL. It must be followed by a previously declared and initialized host-language variable. Without NAME, SET TRANSACTION applies to the default transaction.

The USING option is also ESQL-only. It limits the databases that the transaction can access to the ones mentioned here.

IGNORE LIMBO and LOCK TIMEOUT are not supported in ESQL.

LOCK TIMEOUT and NO WAIT are mutually exclusive.

Default option settings are: READ WRITE + WAIT + SNAPSHOT.

IGNORE LIMBO

Available in: DSQL

Added in: 2.0

Description: With this option, records created by limbo transactions are ignored. Transactions are in limbo if the second stage of a two-phase commit fails.

Note

IGNORE LIMBO surfaces the isc_tpb_ignore_limbo TPB parameter, available in the API since InterBase times and mainly used by gfix.

LOCK TIMEOUT

Available in: DSQL

Added in: 2.0

Description: This option is only available for WAIT transactions. It takes a non-negative integer as argument, prescribing the maximum number of seconds that the transaction should wait when a lock conflict occurs. If the the waiting time has passed and the lock has still not been released, an error is generated.

Note

This is a brand new feature in Firebird 2. Its API equivalent is the new isc_tpb_lock_timeout TPB parameter.

NO AUTO UNDO

Available in: DSQL, ESQL

Added in: 2.0

Description: With NO AUTO UNDO, the transaction refrains from keeping the log that is normally used to undo changes in the event of a rollback. Should the transaction be rolled back after all, other transactions will pick up the garbage (eventually). This option can be useful for massive insertions that don't need to be rolled back. For transactions that don't perform any mutations, NO AUTO UNDO makes no difference at all.

Note

NO AUTO UNDO is the SQL equivalent of the isc_tpb_no_auto_undo TPB parameter, available in the API since InterBase times.