The ACID rules of transaction in any database assure the reliability of data in all transactions in the database.
Atomicity (“all or nothing” rule) - this rule states that either the complete transaction takes place, or none. Even if a part of a transaction fails to work, the complete transaction will fail.
Consistency (only valid data will be written to the database) - this rule ensures that the database is stable, before and after the transaction, even if a transaction fails.
If a Transaction fails, the entire transaction will be rolled back and the database will be restored to a state consistent.
If a Transaction successfully executes, it will take the database from one consistent state to another consistent state.
Isolation (multiple transactions occurring at the same time not impact each other’s execution) - this rule states that when a process is going on in a transaction, the data remains in isolation of other entities in the database.
For example, if Joe issues a transaction against a database at the same time that Mary issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform Joe’s entire transaction before executing Mary’s or vice-versa.
Note that the isolation property does not ensure which transaction will execute first, merely that they will not interfere with each other.
Durability (any transaction committed to the database will not be lost) - this rule states that when a transaction completes successfully, it remains in stable state and is persisted in the database.
Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software or hardware failures.
SQL Server Tips · · · ·