Transactions and the ACID properties

Most database systems support transactions, allowing a number of actions to be grouped together and run with some degree of isolation from transactions being performed by other clients. For example, if you are querying a table in a relational database and someone else is busy modifying the table, you would prefer it if you saw either the state before the changes, or the state after their changes were complete: it would be disconcerting if some of the records returned by the query had the changes, but the others were in the previous state. Database systems differ in the capabilities offered by the transactional model they implement, but there is a common acceptance of a set of desirable features that should be enforced by any transactional system, that have become known as the 'ACID' properties (from their initial letters):

  • Atomic

The changes of a transaction (together with any knock-on changes performed by trigger code attached to the database) are either all applied, or - if there is any reason for any of them to fail - are all cancelled, with the database rolled back to its pre-transactional state.
  • Consistent

Any breach of the database integrity rules causes the transaction to fail, so each successful transaction takes the database from one correct state to another.
(For a relational database system, this would normally mean entity integrity, where rows have to have the right number of columns, values have to be the correct type for the column, 'not null' attributes have to have a value and uniqueness constraints are enforced; primary key integrity, where each table has to have a designated column or set of columns, with each record uniquely identified by the value(s) in its primary key - and referential integrity: if a column or set of columns in a table are defined as a foreign key reference to another table (or even to the same table), then for each record the values in these columns are either all null or they match the primary key of a record in the designated table. In addition, some database systems - including Polyhedra - allow some form of 'trigger code' to be attached to a database that is to be run as part of the transaction, and these rules can impose further constraints.)
  • Isolated

The intermediate state of a transaction is invisible to other transactions.
  • Durable

Once a transaction is reported as successful, the initiator is entitled to assume the changes have been safely records, and so will be preserved even over a system failure.

(It is not sufficient to say that the change will be preserved even if the database system is shut down and restarted; the client has to know that if it has been told the transaction is durable, then it does not matter if the database server immediately crashes.)

Note that an ACID-compliant database system does not necessarily enforce all these guarantees all the time. Thus, it is acceptable if a compliant database system normally offers a weaker form of isolation, for speed reasons, as long as it is possible to set it up to enforce the strict form when required - which could be by starting up the database server in a special way, or by allowing a client application to ask that it is offered the strict guarantees on isolation. Similarly, it might be faster for a database server to normally operate a write-behind journalling mechanism (where the changes are flushed to disk post-transactionally) - but as long as a client is able to detect when 'its' transaction has been fully journalled, the database server can be said to offer a durable service.