|
|
The term concurrency broadly refers to the state in which two or more tasks are running inside the same database at the same time. In these conditions, the database is sometimes said to be supporting parallel tasks.Inside a transaction’s own “bubble,” the owning process will be allowed to perform any operation that
Each transaction is configured by means of a constellation of parameters that enable the client process to predict, with absolute assurance, how the database engine will respond if it detects a potential inconsistency. The engine’s interpretation of “consistency” is governed by the transaction’s configuration. That, in turn, is governed by the client application.
Factors Affecting Concurrency
The four configurable parameters that affect concurrency are
For one level of isolation (READ COMMITTED), the current states of the record versions are also considered.
Isolation Level
Firebird provides three outright levels of transaction isolation to define the “depth” of consistency the transaction requires. At one extreme, a transaction can get exclusive write access to an entire table, while at the other extreme, the uncommitted transaction becomes current with every external change of database state. No Firebird transaction will ever see any uncommitted changes pending for other transactions.
In Firebird, isolation level can be READ COMMITTED, SNAPSHOT, or SNAPSHOT TABLE STABILITY. Within READ COMMITTED, two sub-levels are available:
RECORD_VERSION and NO_RECORD_VERSION.Standard Levels of Isolation
The SQL standard for transaction isolation is “sympathetic” to the two-phase locking mechanism that most RDBMSs use to implement isolation. It is quite idiosyncratic in comparison with many of the other standards. It defines isolation not so much in terms of ideals as in terms of the phenomena each level allows (or denies). The phenomena with which the standard is concerned are
Table shows the four standard isolation levels recognized by the standard, with the phenomena that govern their definitions.
SQL Standard Isolation Levels and Governing Phenomena
READ UNCOMMITTED is not supported in Firebird at all. READ COMMITTED conforms to the standard. At the two deeper levels, the nature of MGA prevails over the two-phase locking limitations implied by the standard. Mapping to the standard governance of REPEATABLE READ and SERIALIZABLE is not possible.
READ COMMITTED
The shallowest level of isolation is READ COMMITTED. It is the only level whose view
of database state changes during the course of the transaction since, every time the latest committed version of a record it is accessing changes, the newly committed record version replaces the version the transaction began with. Inserts committed since this transaction began are made visible to it.
By design, READ COMMITTED isolation allows non-repeatable reads and does not prevent the phenomenon of phantom rows. It is the most useful level for high-volume, real-time data entry operations because it reduces data contention, but it is unsuitable for tasks that need a reproducible view.
Because of the transient nature of READ COMMITTED isolation, the transaction (ThisTransaction) can be configured to respond more conservatively to external commits and other pending transactions:
SNAPSHOT (Concurrency)
The “middle” level of isolation is SNAPSHOT, alternatively termed Repeatable Read or Concurrency. However, Firebird’s SNAPSHOT isolation does not accord exactly with Repeatable Read as defined by the standard. It isolates the transaction’s view from row-level changes to existing rows. However, because the MGA architecture, by nature, completely isolates SNAPSHOT transactions from new rows committed by other transactions, by denying SNAPSHOT transactions access to the global transaction state bitmap, there is no possibility of SNAPSHOT transactions seeing phantom rows. Hence, a Firebird SNAPSHOT transaction provides a deeper level of isolation than the SQL REPEATABLE READ.
Yet SNAPSHOT is not identical to SERIALIZABLE, because other transactions can update and delete rows that are in the purview of the SNAPSHOT transaction, provided they post first.
The transaction is guaranteed a non-volatile view of the database that will be unaffected by any changes committed by other transactions before it completes. It is a useful level for “historical” tasks like reporting and data export, which would be inaccurate if not performed over a reproducible view of the data.
SNAPSHOT is the default isolation level for the isql query tool and for many component and driver interfaces.
SNAPSHOT TABLE STABILITY (Consistency)
The “deepest” level of isolation is SNAPSHOT TABLE STABILITY, alternatively termed Consistency because it is guaranteed to fetch data in a non-volatile state that will remain externally consistent throughout the database as long as the transaction lasts. Read-write transactions cannot even read tables that are locked by a transaction with this isolation level.
The table-level locking imposed by this isolation comprises all tables accessed by the transaction, including those with referential constraint dependencies.
This level constitutes an aggressive extension that guarantees serialization in the strict sense that no other transaction can insert or delete—or indeed, change—rows in the tables involved if any transaction succeeds in acquiring a handle with this isolation. Conversely, the TABLE STABILITY transaction will not be able to acquire a handle if any read-write transaction is currently reading any table that is in its purview. In terms of the standard, it is unnecessary, since SNAPSHOT isolation already protects transactions from all three of the phenomena governed by the SQL standard SERIALIZABLE level.
A consistency transaction is also referred to as a blocking transaction because it blocks access by any other read-write transaction to any of the records that it accesses and to any records that depend on those records.
Access Mode
Access mode can be READ WRITE or READ ONLY. A READ WRITE transaction can select, insert, update, and delete data. A READ ONLY transaction can only select data. The default access mode is READ WRITE.
Lock Resolution Mode (“Blocking Mode”)
Lock resolution mode determines behavior in the event the transaction (ThisTransaction) tries to post a change that conflicts with a change already posted by another transaction. The options are WAIT and NOWAIT.
WAIT
WAIT (the default) causes the transaction to wait until rows locked by a pending transaction are released, before determining whether it can update them. At that point, if the other transaction has posted a higher record version, the waiting transaction will notify that a lock conflict has occurred.
WAIT is often not the preferred blocking mode in high-volume, interactive environments because of its potential to slow down the busiest users and, in some conditions, to cause “livelocks” (see the section “What Is a Deadlock?”).
WAIT is virtually pointless in SNAPSHOT isolation. Unless the blocking transaction eventually rolls back—the least likely scenario—the outcome of waiting is certain to be a lock conflict, anyway. In a READ COMMITTED transaction, the likelihood that the outcome of waiting would be a lock conflict is much reduced.
That is not to deny the usefulness of WAIT for some conditions. If the client application’s exception handler handles conflicts by continually retrying without pausing, the bottlenecking caused by repeated retries and failures is likely to be worse than if WAIT is specified, especially if the blocking transaction takes a long time to complete. By contrast, WAIT is potentially going to cause one exception, eventually handled by one rollback.
Where the likelihood of transactions colliding is high but transactions are short, WAIT is to be preferred because it guarantees that waiting requests will proceed in a FIFO sequence, rather than be required to take their chances with each repeated request. However, in user environments where a quick turnover cannot be guaranteed, WAIT transactions are contraindicated because of their potential to hold back garbage collection.
NO WAIT
In a NO WAIT transaction, the server will notify the client immediately if it detects a new, uncommitted version of a row the transaction tries to change. In a reasonably busy multi-user environment, NO WAIT is sometimes preferable to the risk of creating bottlenecks of waiting transactions.
As a rule of thumb for SNAPSHOT transactions, throughput will be faster and interfaces more responsive if the client application chooses NO WAIT and handles lock conflicts through the use of rollback, timed retries, or other appropriate techniques.
Table Reservation
Firebird supports a table locking mode to force full locks on one or more tables for the duration of a transaction. The optional RESERVING <list of tables> clause requests immediate full locks on all committed rows in the listed tables, enabling a transaction to guarantee itself exclusive access at the expense of any transactions that become concurrent with it.
Unlike the normal locking tactic, reservation locks all rows pessimistically—it takes effect at the start of the transaction, instead of waiting until the point at which an individual row lock is required.
Table reservation has three main purposes:
You can reserve more than one table in a transaction.
Uses of Table Reservation
Using table reservation with SNAPSHOT or READ COMMITTED isolation is recommended in preference to using SNAPSHOT TABLE STABILITY when table-level locking is required. Table reservation is the less aggressive and more flexible way to lock tables pre-emptively. It is available for use with any isolation level. However, using it in combination with SNAPSHOT TABLE STABILITY is not recommended, because it has no effect in mitigating access restrictions on tables that the transaction might access that are outside the scope of the RESERVING clause.
Pre-emptive table locking is not for everyday use, but it can be usefully employed for a task such as a pre-audit valuation or a “stock freeze” report prior to a stocktake.
Parameters for Table Reservation
Each table reservation can be configured with distinct attributes to specify how multiple transactions should be treated when they request access to a reserved table.
The choices are
[PROTECTED | SHARED] {READ | WRITE}The PROTECTED attribute gives ThisTransaction an exclusive lock on the table it is reading and allows any other transaction that is in SNAPSHOT or READ COMMITTED isolation to read rows. Writes are restricted by one of the two modifiers:
The SHARED attribute lets any SNAPSHOT or READ COMMITTED transaction read the table and provides two options for concurrent updating by other transactions:
|
|
Firebird Related Tutorials |
|
---|---|
MySQL Tutorial | Linux Tutorial |
Windows 10 Tutorial | Windows 10 Development Tutorial |
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.