SET TRANSACTION

<< SAVEPOINT | FB 2.5 Language Reference | PSQL statements >>
<< SAVEPOINT | FB 2.1 Language Reference | PSQL statements >>
<< SAVEPOINT | FB 2.0 Language Reference | PSQL statements >>

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.

back to top of page

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.

back to top of page

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.

See also:
Firebird Interactive SQL Utility: Transaction Handling
SET TRANSACTION
Transaction
COMMIT
ROLLBACK
SET NAMES
Transaction options explained
Data transaction
Database statistics
Firebird 2.1 monitoring tables
Firebird 2.1 Release Notes: Lock timeout for WAIT transactions
Firebird 2.0 & 2.1 Release Notes: SET TRANSACTION enhanced
Firebird for the database expert: episode 4 - OAT, OIT and sweep
Firebird for the database expert: episode 5 - Locking and record versions
Multi-generational architecture (MGA) and record versioning
Multi-version concurrency control

back to top of page
<< SAVEPOINT | FB 2.5 Language Reference | PSQL statements >>
<< SAVEPOINT | FB 2.1 Language Reference | PSQL statements >>
<< SAVEPOINT | FB 2.0 Language Reference | PSQL statements >>