Subject Re: [firebird-support] Transaction Parameter Block Options
Author Ann W. Harrison
pierre.yager@... wrote:
> I'm writing an article about Firebird Transactions (in French...) and I
> would like to know if you can help me to explain what are these parameters
> for Transaction Parameter Block, many are not documented in Interbase
> 6.0 Documentation Set :
> #define isc_tpb_version1 1
> TPB version 1

All interface objects have version numbers so that their
semantics can be changed if necessary. The first element
in the object is the version number.

> #define isc_tpb_version3 3
> TPB version 3

And in this case it was necessary.

> #define isc_tpb_consistency 1
> Lock-based transaction scheme. Useful for SERIALIZABLE isolation
level in conjuction with isc_tpb_lock_read / isc_tpb_lock_write and
isc_tpb_exclusive ?

Consistency does table locking using a protected lock and gives
serializable results. It can be used with lock_read and lock_write to
produce deadlock-free, serializable results, essentially by serializing
the transactions.

Whenever a table is used, regardless of the mode, the transaction takes
a lock on it. In concurrency and read_committed the mode is shared read
or shared write. Shared modes are compatible with each other In
consistency, the mode is protected read or protected write. Protected
read is compatible with shared read and other protected read locks.
Protected write is compatible with shared read.
> #define isc_tpb_concurrency 2
> MGA based transaction scheme. Used for REPEATABLE
READ isolation level

No, used for SNAPSHOT isolation. REPEATABLE READ, as defined by the SQL
standards committee allows phantoms.
> #define isc_tpb_shared 3
> Shared table reservation mode for lock based transaction scheme.

Allows a table to be reserved in shared read or write mode. There is no
advantage to using this type of table reservation because it does not
prevent deadlocks.

> #define isc_tpb_protected 4
> Protected table reservation mode for lock based transaction scheme.

Locking tables in protected mode at transaction start - if done
consistently - allows a system to run deadlock free, at the cost of
delayed transaction starts.
> #define isc_tpb_exclusive 5
> ??? (not documented) : Exclusive table reservation mode for lock based
transaction scheme. I think I have to use it to simulate SERIALIZABLE
isolation level.

In fact, exclusive translates internally to protected. Although many
people think they need exclusive use of a table or set of tables to do
some particular transformation, in fact, they don't. The changes of a
running transaction are invisible to other transactions, so the effect
is the same as if the concurrent transactions ran before the one that is
making the changes. Protected write table locks will keep other
transactions from making changes, which is sometimes necessary.
> #define isc_tpb_wait 6
> Indicated wheter the transaction should wait for others pending
> transactions to commit before accessing the records.

No. When a "wait" transaction encounters an update conflict - a record
it wants to update has already been updated by a concurrent transaction
- it waits for the other transaction to finish before raising an error
(if the other transaction commits) or continuing with its operation (if
the other transaction rolls back). In heavy contention, a "wait"
transaction prevents what is called live-lock. A live-lock occurs when
two transactions are trying to change the same set of records in
different order. If both quit when the conflict is first discovered,
each will start again, and they'll just run into each other again, and
again and again ... If one waits for the other, at least one of them
will succeed and go on to do something else.
> #define isc_tpb_nowait 7
> Indicate the transaction to raise an exception immediately
if pending transactions are accessing the same records.

Not just accessing - both must be deleting or updating the same records.
> #define isc_tpb_read 8
> The transaction is read only
> #define isc_tpb_write 9
> The transaction has read/write access rights.
> #define isc_tpb_lock_read 10
> Specifies which tables are locked for non concurrent
reads (other than isc_tpc_read_commited and isc_tpb_concurrency)

Not exactly. Specifies that a table reservation is for read only even
though the transaction may be read/write.
> #define isc_tpb_lock_write 11
> Specifies which tables are locked for non concurrent
writes (see isc_tpb_lock_read)

Specifies that a table reservation is for read/write access.
> #define isc_tpb_verb_time 12
> ??? not documented.

Not implemented. This is related to deferred constraints. Constraints
can execute at verb time or commit time. Firebird implements only verb
time constraints.
> #define isc_tpb_commit_time 13

See above.
> #define isc_tpb_ignore_limbo 14
> ??? used by gbak to ignore limbos ?
Used by any transaction that needs to be able to access data that may
include records in limbo.
> #define isc_tpb_read_committed 15
> READ COMMITED isolation level.
> #define isc_tpb_autocommit 16

Autocommit this transaction - every statement is a separate transaction.

> #define isc_tpb_rec_version 17
> A transaction can always read the last commited version of
a record even if there are pending transactions that have modified
this record.
> #define isc_tpb_no_rec_version 18
> A transaction has to wait (or report an error if no_wait
specified) for all other pending transactions to commit before
reading the latest version of this record.
> #define isc_tpb_restart_requests 19

Hmm... It appears that it looks for requests in the connection which
had been active in another transaction, unwinds them, and restarts them
under the new transaction.
> #define isc_tpb_no_auto_undo 20

By default the system keeps an undo log. In the case of large inserts -
like gbak when it's populating a database - the undo log is unnecessary
> #define isc_tpb_lock_timeout 21
Firebird 2 has a wait with timeout.
> As a bonus, can you help me defining wich parameters should be used to simulate all standard and non standard SQL isolations levels ?
> READ UNCOMMITED : not allowed by Firebird
> READ COMMITED : isc_tpc_read_commited, isc_tpb_rec_version, isc_tpb_no_wait

Can be either wait or no wait. Wait is preferred.

> CURSOR STABILITY : ??? i believe it should use isc_tpb_read_commited + something ?

Read committed plus commit retaining.

> REPEATABLE READ : isc_tpb_concurrency

Actually concurrency is more consistent than repeatable read.;

> SNAPSHOT : ???


> SERIALIZABLE : isc_tpb_consistency, isc_tpb_wait, isc_tpb_lock_write (all tables),

consistency, wait, lock_read (read only tables) lock_write (read write

Hope this helps.