You must be familiar with these two terms, but you might have knowledge that they both are very much interrelated. I will try to give some heads up on this.
ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. No database that fails to meet any of these four goals can be considered reliable.
- Atomicity: It depends on one action – all or nothing, i.e. if there’s a transaction between two different pieces of information, either all the pieces are committed or none of them.
- Consistency: The database must begin and end the transaction in a consistent state, i.e. if any failure occurs, should return the data in a state before the transaction was started.
- Isolation: It means that no transaction should affect another transaction, i.e. the transactions which are in process and not yet committed should remain isolated from any other transaction.
- Durable: It means that the transaction once committed, always be committed, i.e. Committed data should be saved by the system because in the event of failure and when system restarts, the data is available in its current state.
SQL Server’s Transaction Log is an integral to SQL Server’s reputation for data integrity and robustness. The transaction log is very much vital to the ACID capabilities of SQL Server. SQL Server writes to the transaction log as soon as any change to the data page happens.
Each and every DML statement, i.e. Select, Insert, Update, Delete is a complete transaction, and the transaction log ensures that the entire set-based operation takes place, thereby ensuring the atomicity of the transaction.
SQL Server can use the transaction log to roll back, or complete a transaction regardless of hardware failure, which is the key to both the consistency and durability of the transaction.
Hope you like it. I planned to write more on transaction logs…