SQL Server – ACID and SQL Server’s Transaction Log, Are they Related???

Hey Folks,

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…

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published. Required fields are marked *