Question

What does each letter in ACID stand for in the context of database transactions? Describe a concrete example (i.e. a scenario) to illustrate the property the letter "I" refers to, in terms how it works or how it breaks in the example.

Answer 1

ACID stands for Atomicity, Consistency, Isolation, and Durability.

The isolation portion of the ACID Properties is needed when there are concurrent transactions. Concurrent transactions are transactions that occur at the same time, such as shared multiple users accessing shared objects. The safeguards used by a DBMS to prevent conflicts between concurrent transactions are a concept referred to as isolation.

Isolation allow multiple transactions to occur at the same time without impacting the execution of each.

How Isolation Works

It is important to note that the isolation property does not ensure that a specific transaction will execute first, only that they will not interfere with each other

Let X= 500, Y = 500.
Consider two transactions T and T”.

Suppose T has been executed till Read (Y) and then T’’ starts. As a result , interleaving of operations takes place due to which T’’ reads correct value of X but incorrect value of Y and sum computed by
T’’: (X+Y = 50, 000+500=50, 500)
is thus not consistent with the sum at end of transaction:
T: (X+Y = 50, 000 + 450 = 50, 450).
This results in database inconsistency, due to a loss of 50 units. Hence, transactions must take place in isolation and changes should be visible only after they have been made to the main memory.