ACID properties

Consistency ensures that a transaction can only bring the database from one valid state to another, maintaining database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof. This prevents database corruption by an illegal transaction, but does not guarantee that a transaction is correct.

Transactions are often executed concurrently. Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially. There are four layers: read uncommitted, read committed, repeatable reads and serializable. In PostgreSQL the default one is READ COMMITED, meaning that when a transaction runs on this isolation level, a SELECT query sees only data committed before the query began and never sees either uncommitted data or changes committed during query execution by concurrent transactions.

Durability is the property which guarantees that transactions that have committed will survive permanently. For example, if a flight booking reports that a seat has successfully been booked, then the seat will remain booked even if the system crashes.

The ANSI/ISO standard SQL 92 refers to three different read phenomena when a transaction reads data that another transaction might have changed. These are dirty reads (when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed), non-repeatable reads (during the course of a transaction, a row is retrieved twice and the values within the row differ between reads) and phantom reads (when, in the course of a transaction, new rows are added or removed by another transaction to the records being read).