SET TRANSACTION

SET TRANSACTION

IGNORE LIMBO
LOCK TIMEOUT
NO AUTO UNDO

`SET TRANSACTION`

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.