ACID Compliance and Firebird

Atomicity

Atomicity guarantees that there are only two possible outcomes from a task (known as a transaction) that involves changing multiple sets of interdependent data: either every step in the task completes successfully and can be committed to the database as a single unit or, if one step should fail, all other steps can be undone (“rolled back”), leaving the global state of the database unchanged.

Atomicity is obviously of extreme importance in financial systems, where imbalances caused by partial failures could be catastrophic. Spreadsheets, and database systems that do not support transactions, cannot be atomic.

Firebird is completely “transaction-driven”: nothing occurs outside a transaction context.

Consistency

Consistency is the capability of the database engine to protect a database from any change of state that could leave any set of data in out-of-synch with any other set. For example, the system has to be capable of recognising that an invoice depends on a customer and is itself depended on by invoice line items. It must be able to prevent, for example, deletion of a customer record if there are invoices stored for that customer, and deletion of an invoice that has line items associated with it.

The practical implementation of such dependencies is by means of declarative referential integrity constraints (“foreign keys”) enforced by automatically-generated triggers. (Triggers are automatically-generated or user-defined blocks of executable code that run whenever a record is inserted, modified or deleted.) Database systems that rely on application code to enforce consistency rules do not comply with the consistency principle. Firebird supports the full range of referential integrity rules defined by the standard.

Firebird also guarantees consistency when a single transaction is performing changes across multiple databases, by means of what is termed “two-phase commit”. Systems that can access mutliple databases concurrently without the ability synchronise changes in all of them will fail on the consistency principle.

Note

Firebird does not support declarative referential integrity across database boundaries. Each database involved in multi-database transactions is required to be responsible for its own RI.

Isolation

Isolation refers to the capability of the database to allow each user (or transaction) to operate as though it were the only user (or transaction). The isolation mechanism must be capable of keeping each transaction's view of database state consistent as long as that transaction is running, regardless of any changes that are performed by other transactions. Database management systems that comply with this principle usually offer a range of isolation levels, the rules for which are defined in the ISO/ANSI SQL standards.

In addition to the level described above (Concurrency or Repeatable Read isolation), which must be supported, Firebird supports Read Committed (where a transaction's view of database state is kept up-to-date with work committed by other transactions) and, on the other side, Consistency or Table Stability (where a transaction that is capable of writing to the database blocks other write-capable transactions from accessing the tables that it is reading).

Durability

Durability guarantees that the database will keep track of pending changes in such a way that the state of the database is not affected if a transaction is interrupted. Hence, even if the database server is unplugged in the middle of a transaction, ACID-compliant database servers must return databases to a consistent state when restarted.

Transaction Logging

Durability is one of the hardest principles to comply with. Other database management systems that claim ACID support have traditionally dealt with it by storing uncommitted transactions in a transaction log. However, logging never totally guarantees durability, since the log file itself may be logically or physically corrupted by the event that interrupts the transaction.

Some DBMSs that rely on logging to achieve durability try to reduce that risk by using a “write-ahead log” to log requests to disk before attempting to post changes. If the write-ahead log survivesundamaged, it may be possible to retrieve uncommitted work when the system recovers and use it to reconcile database state and restore it as it was before the event. Such systems are characterised by the need for a lengthy “recovery procedure” after network or power failures.

Certain high-profile DBMS products are notorious for log-related breakages resulting from interrupted transactions. The instability of these database engines is such that, even on sites with moderate requirements, it becomes a necessity to employ staff to babysit the server around the clock to keep it out of trouble and fix breakages before problems propagate too deeply to save data integrity.

Firebird's Multi-generational Architecture

Firebird's architecture avoids the need for recovery logging by literally retaining the preceding version of every deleted or changed record, not just for the duration of the transaction but until all transactions that were “interested” in that record, for any reason at all, have ended. The term for this is “multi-generational architecture”, or MGA.

MGA was unique to InterBase for about 10 years until it was imitated by Oracle. Once the Firebird sourcecode was available, PostGreSQL copied it. More recently, Microsoft has introduced MGA in the latest evolution of SQL Server.

Transaction Logging and Firebird

Firebird does not need transaction logging for recovery purposes and it does not include any transaction logging facilities in the engine. However, where enterprises require logging for auditing purposes, some excellent logging service software is available from third party vendors.