| Subject | Re: [firebird-support] Transaction Parameter Block Options |
|---|---|
| Author | Ann W. Harrison |
| Post date | 2005-04-11T19:32:15Z |
pierre.yager@... wrote:
semantics can be changed if necessary. The first element
in the object is the version number.
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.
No, used for SNAPSHOT isolation. REPEATABLE READ, as defined by the SQL
standards committee allows phantoms.
advantage to using this type of table reservation because it does not
prevent deadlocks.
consistently - allows a system to run deadlock free, at the cost of
delayed transaction starts.
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.
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.
Not just accessing - both must be deleting or updating the same records.
Not exactly. Specifies that a table reservation is for read only even
though the transaction may be read/write.
Specifies that a table reservation is for read/write access.
can execute at verb time or commit time. Firebird implements only verb
time constraints.
include records in limbo.
this record.
reading the latest version of this record.
had been active in another transaction, unwinds them, and restarts them
under the new transaction.
like gbak when it's populating a database - the undo log is unnecessary
overhead.
consistency, wait, lock_read (read only tables) lock_write (read write
tables).
Hope this helps.
Ann
>All interface objects have version numbers so that their
> 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
semantics can be changed if necessary. The first element
in the object is the version number.
> #define isc_tpb_version3 3And in this case it was necessary.
> TPB version 3
> #define isc_tpb_consistency 1level in conjuction with isc_tpb_lock_read / isc_tpb_lock_write and
>
> Lock-based transaction scheme. Useful for SERIALIZABLE isolation
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.
>READ isolation level
> #define isc_tpb_concurrency 2
>
> MGA based transaction scheme. Used for REPEATABLE
No, used for SNAPSHOT isolation. REPEATABLE READ, as defined by the SQL
standards committee allows phantoms.
>Allows a table to be reserved in shared read or write mode. There is no
> #define isc_tpb_shared 3
>
> Shared table reservation mode for lock based transaction scheme.
advantage to using this type of table reservation because it does not
prevent deadlocks.
>Locking tables in protected mode at transaction start - if done
> #define isc_tpb_protected 4
>
> Protected table reservation mode for lock based transaction scheme.
consistently - allows a system to run deadlock free, at the cost of
delayed transaction starts.
>transaction scheme. I think I have to use it to simulate SERIALIZABLE
> #define isc_tpb_exclusive 5
>
> ??? (not documented) : Exclusive table reservation mode for lock based
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.
>No. When a "wait" transaction encounters an update conflict - a record
> #define isc_tpb_wait 6
>
> Indicated wheter the transaction should wait for others pending
> transactions to commit before accessing the records.
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.
>if pending transactions are accessing the same records.
> #define isc_tpb_nowait 7
>
> Indicate the transaction to raise an exception immediately
Not just accessing - both must be deleting or updating the same records.
>reads (other than isc_tpc_read_commited and isc_tpb_concurrency)
> #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
Not exactly. Specifies that a table reservation is for read only even
though the transaction may be read/write.
>writes (see isc_tpb_lock_read)
> #define isc_tpb_lock_write 11
>
> Specifies which tables are locked for non concurrent
Specifies that a table reservation is for read/write access.
>Not implemented. This is related to deferred constraints. Constraints
> #define isc_tpb_verb_time 12
>
> ??? not documented.
can execute at verb time or commit time. Firebird implements only verb
time constraints.
>See above.
> #define isc_tpb_commit_time 13
>Used by any transaction that needs to be able to access data that may
> #define isc_tpb_ignore_limbo 14
>
> ??? used by gbak to ignore limbos ?
include records in limbo.
>Autocommit this transaction - every statement is a separate transaction.
> #define isc_tpb_read_committed 15
>
> READ COMMITED isolation level.
>
> #define isc_tpb_autocommit 16
>a record even if there are pending transactions that have modified
> #define isc_tpb_rec_version 17
>
> A transaction can always read the last commited version of
this record.
>specified) for all other pending transactions to commit before
> #define isc_tpb_no_rec_version 18
>
> A transaction has to wait (or report an error if no_wait
reading the latest version of this record.
>Hmm... It appears that it looks for requests in the connection which
> #define isc_tpb_restart_requests 19
had been active in another transaction, unwinds them, and restarts them
under the new transaction.
>By default the system keeps an undo log. In the case of large inserts -
> #define isc_tpb_no_auto_undo 20
like gbak when it's populating a database - the undo log is unnecessary
overhead.
>Firebird 2 has a wait with timeout.
> #define isc_tpb_lock_timeout 21
>
>Can be either wait or no wait. Wait is preferred.
>
> 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
> CURSOR STABILITY : ??? i believe it should use isc_tpb_read_commited + something ?Read committed plus commit retaining.
> REPEATABLE READ : isc_tpb_concurrencyActually concurrency is more consistent than repeatable read.;
> SNAPSHOT : ???Concurrency.
> SERIALIZABLE : isc_tpb_consistency, isc_tpb_wait, isc_tpb_lock_write (all tables),isc_tpb_exclusive
consistency, wait, lock_read (read only tables) lock_write (read write
tables).
Hope this helps.
Ann
>consist