ACID properties

                  

Scenario: ACID properties for DB systems

Solution:

A transaction is a sequence of operations that are executed as a single unit of work, and a transaction may consist of one or many steps. A transaction access data using read and write operations.

Transaction guerentees the  integrity and consistency of the data. If a transaction succeeds, the data that were modified during the transaction will be saved, on error the change to data will not be applied.

Atomicity [Transaction Manager]

A transaction must be an atomic unit of work, wither all the data is modified or none. The transaction should be completely executed or fails completely, if one part of the transaction fails, all the transaction will fail.

Ex: During money transfer if money is going out from account A and to account B, both operations should be executed together, and if one of them fails, the other will not be performed and the earlier ones rolled back or not applied. 

Consistency [DB Engineer]

The transaction maintains data integrity constraints and so data is in consistent state (enforced by  constraints, cascades, and triggers). The database before and after the transaction should be consistent. If a transaction makes data in invalid state, the transaction is aborted and an error is reported.

Ex: If we try to add a record in a User table with the ID of a department that does not exist in the Department table, the transaction will fail. Or as in example for Atomicity if the user A's money is deducted and its not credited to user B then the amounts would be inconsistent.

Isolation [Application programmer]

The transaction should  not be changed by any other concurrent transaction, so each transaction in progress will should not be interfered by any other transaction until it is completed.

A transaction before altering any row is locked (shared or exclusive) on that row, disallowing any other transaction to act on it. The folowing transactions need to wait until the first one either commits or rollbacks.

Ex: There is only one stock available for a Product on online store the two shoppers buying at the same time only the first user's transaction finishes and the transaction of the other user be interrupted and not go through.

Durability [Recovery Manager]

Once a transaction is completed and committed, its changes are persisted permanently in the database. The data saved in the database is immutable until another update or deletion transaction affects it.

So when transaction is committed, it will remain in this state even if there are any issue later like m/c crash system restart etc. The completed transactions are recorded on permanent memory devices like hard drives, so the data will be always available even when the DB instance is restarted.