SQL | ACID Properties

ACID properties

What are ACID properties?
A transaction is a single logical unit of work that accesses and possibly modifies the contents of a database. Transactions access data using read-and-write operations. In order to maintain consistency in a database, before and after the transaction, certain properties are followed.
These are called ACID properties. ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably.

Atomicity:
By this, we mean that either the entire transaction takes place at once or doesn’t happen at all. There is no midway i.e. transactions do not occur partially.
Each transaction is considered as one unit and either runs to completion or is not executed at all. It involves the following two operations.
—Abort: If a transaction aborts, changes made to the database are not visible.
—Commit: If a transaction commits, changes made are visible.
Atomicity is also known as the ‘All or nothing rule’.

Let Before Transaction T0 startsion X= 500, Y = 200.

Transaction T0
    Transaction T1
    Read(X)
    X= X-100
    Write(X)

    Transaction T2
    Read(Y)
    Y= Y+100
    Write(Y)


If the transaction fails after completion of T1 but before completion of T2.
Then the amount has been deducted from X but not added to Y.
This results in an inconsistent database state. Therefore, the transaction must be executed in its entirety in order to ensure the correctness of the database state.




Consistency:
This means that integrity constraints must be maintained so that the database is consistent before and after the transaction. It refers to the correctness of a database.
The total amount before and after the transaction must be maintained.
Total before T0 occurs = 500 + 200 = 700.
Total after T0 occurs = 400 + 300 = 700.
Therefore, the database is consistent. Inconsistency occurs in case T1 completes but T2 fails. As a result, T is incomplete.



Isolation:
This property ensures that multiple transactions can occur concurrently without leading to the inconsistency of the database state.
Transactions occur independently without interference. Changes occurring in a particular transaction will not be visible to any other transaction until that particular change in that transaction is written to memory or has been committed.
This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved these were executed serially in some order.

Let Before Both X= 500, Y = 500.

Transaction T1
Read(X)
X= X*100
Write(X)
Read(Y)
Y= Y-50
Write(Y)

Transaction T2
Read(X)
Read(Y)
Z= X+Y
Write(Z)

Suppose T1 has been executed till Read (Y) and then T2 starts. As a result, interleaving of operations takes place due to which T2 reads the correct value of X but the incorrect value of Y and sum computed by
T2: (X+Y = 50000 + 500 = 50500)
is thus not consistent with the sum at end of the transaction:
T1: (X+Y = 50000 + 450 = 50450).



Durability:
This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs. These updates now become permanent and are stored in non-volatile memory. The effects of the transaction, thus, are never lost.