MGA, an acronym for multi-generational architecture, refers to the architectural model underlying Firebird’s database state management.
In the MGA model, each row stored in the database holds the unique transaction ID of the transaction that writes it. If another transaction posts a change to the row, the server writes a new version of the row to disk, with the new transaction ID, and converts an image of the older version into a reference (known as the delta) to this new version. The server now holds two “generations” of the same row.
Post vs. COMMIT
The term “post” has probably been borrowed from older desktop accounting software as an analogy to the posting of journals in accounting systems. The analogy is useful for distinguishing the two separate operations of writing a reversible change to the database (by executing a statement) and committing all of the changes executed by one or several statements. Posted changes are invisible beyond the boundaries of their transaction context and can be rolled back. Committed changes are permanent and become visible to transactions that are subsequently started or updated.
If any conflict occurs upon posting, the server returns an exception message to the client and the commit fails. The application must then deal with the conflict according to its kind. The solution to an update conflict is often to roll back the transaction, causing all of the work to be “undone” as an atomic unit. If there is no conflict, the application can proceed to commit the work when it is ready.
Rollback never fails. It will undo any changes that were requested during the transaction—the change that caused the exception as well as any that would have succeeded had the exception not occurred.
Some rolled-back transactions do not leave row images on the disk. For example, any rollbacks instigated by the server to undo work performed by triggers simply vanish, and the record images created by inserts are normally expunged during rollback, with reference to an auto-undo log that is maintained for inserts. If a single transaction inserts a huge number of records, the auto-undo log is abandoned, with the result that a rollback will leave the images on disk. Other conditions that may cause inserted record images to remain on disk include server crashes during the operation or use of a transaction setting that explicitly requests “no auto-undo.”
Under MGA, the existence of a pending new version of a row has the effect of locking it. In most conditions, the existence of a committed ewer version blocks a request to update or delete the row as well—a locking conflict.
On receiving a request to update or delete, the server inspects the states of any transactions that “own” newer versions of the row. If the newest of those owner transactions is active or has been committed, the server responds to the requesting transaction according to the context (isolation level and lock resolution parameters) of the requesting transaction.
If the newest version’s transaction is active, the requesting transaction will, by default, wait for it to be completed (committed or rolled back), and then the server will allow it to proceed. However, if NOWAIT was specified, it returns a conflict exception to the requesting transaction.
If the newest version’s transaction is committed and the requesting transaction is in SNAPSHOT (i.e., concurrency) isolation, the server refuses the request and reports a lock Conflict. If the transaction is in READ COMMITTED isolation, with the default RECORD_VERSION setting, the server allows the request and writes a new record version on behalf of the transaction.
Firebird does not use conventional two-phase locking at all for the most usual transaction contexts. Hence, all normal locking is at the row level and is said to be optimistic —each row is available to all read-write transactions until a writer creates a newer version of it.
Upon a successful commit, the old record version referenced by the delta image becomes an obsolete record.
In summary, under normal conditions:
A transaction can be configured to lock whole tables. There are two acceptable ways to do this in DSQL: by isolating the transaction in SNAPSHOT TABLE STABILITY (aka consistency mode, forced repeatable read) or by table reservation. It should be emphasized that these configurations are for when unusually pre-emptive conditions are required. They are not recommended in Firebird for everyday use.
An unacceptable way to impose a table-level lock (Firebird 1.5 and later) is to apply a statement-level pessimistic lock that affects the whole table. A statement such as the following can do that:SELECT * FROM ATABLE FOR UPDATE WITH LOCK;
It is not, strictly speaking, an issue of transaction configuration. However, the configuration of the transaction that owns sets retrieved with this explicit pessimistic lock is important.
There are no deltas or locks for inserts. If another transaction has not pre-empted inserts by a table-level lock, an insert will always succeed if it does not violate any constraints or validation checks.
Firebird Related Interview Questions
|RDBMS Interview Questions||MySQL Interview Questions|
|Linux Interview Questions||Mac OS X Deployment Interview Questions|
|Windows Administration Interview Questions||Windows Server 2003 Interview Questions|
|SQL Interview Questions||NoSQL Interview Questions|
|Advanced C++ Interview Questions|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.