Database transaction ACID

1. In database systems, atomicity (or atomicness) is one of the ACID transaction properties. In an atomic transaction, a series of database operations either all occur, or nothing occurs.

An example of atomicity is ordering an airline ticket where two actions are required: payment, and a seat reservation. The potential passenger must either:
  1. both pay for and reserve a seat; OR
  2. neither pay for nor reserve a seat.
The booking system does not consider it acceptable for a customer to pay for a ticket without securing the seat, nor to reserve the seat without payment succeeding.

2. In database systems, a consistent transaction is one that does not violate any integrity constraints during its execution.  

    * Entity integrity
    * Referential Integrity
    * Domain Integrity

For example, if you define the attribute of Age of an Employee entity, as an integer, the value of every instance of that attribute must always be numeric and an integer.
If you also define that this attribute must always be positive, then a negative value is forbidden.

3. In database systems, isolation is a property that defines how/when the changes made by one operation become visible to other concurrent operations.

The isolation property is the most often relaxed ACID property in a DBMS (Database Management System). This is because to maintain the highest level of isolation a DBMS usually acquires locks on data, which may result in a loss of concurrency, or else implement multiversion concurrency control, which may require additional application logic to function correctly.

Most DBMSs offer a number of transaction isolation levels which control the degree of locking which occurs when selecting data. For many database applications the majority of database transactions can be constructed in such a way as to not require high isolation levels, thus reducing the locking overhead for the system. The programmer must carefully analyze database access code to ensure that any relaxation of isolation does not cause difficult-to-find software bugs. Conversely, at higher isolation levels the possibility of deadlock is increased, which also requires careful analysis and programming techniques to avoid.

4. In database systems, durability is the ACID 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.
Durability can be achieved by flushing the transaction's log records to non-volatile storage before acknowledging commitment. In distributed transactions, all participating servers must coordinate before commit can be acknowledged. This is usually done by a two-phase commit protocol.