Today I want to take you all back to one of the very most common interview questions and answers which I see often discussed in more interviews.
Transaction
A transaction is a batch of SQL statements that behaves like a single unit. In simple words, a transaction is a unit where a sequence of work is done to complete the whole activity. We can take an example of a Bank transaction to understand this.
ACID Properties in SQL Server
ACID Properties in SQL Server ensure Data Integrity during a transaction. Here transaction is a group of SQL (insert, update, and delete) statements that are treated as a single unit, and they are executed by applying the principle either completed or Not. A successful transaction must Follow the ACID Properties. The ACID is an acronym for Atomicity, Consistency, Isolation, and Durability.
Atomicity
Every transaction follows atomicity. It means that if a transaction is started, it should be either completed or rollback. To understand this, let's take the above example, if a person is transferring an amount from account “A” to account “B”, it should be credited to account B after completing the transaction. In case any failure happens, after debiting the amount from account “A", the change should be rollback. In simple words, Each transaction will either succeed or be Rollback.
Consistency
Consistency says that after the completion of a transaction, changes made during the transaction should be consistent. Let’s understand this fact by referring to the above example, if account “A” has been debited by 200 RS, then after the completion of the transaction, account “B” should be credited by 200 RS. It means changes should be consistent. In simple words, Consistency means a guarantee that a transaction never leaves your database in a half-finished state.
Isolation
Isolation states that every transaction should be isolated from each other. There should not be any interference between the two transactions. In simple words, Any other operation cannot affect my operation.
Durability
Durability means that once the transaction is completed, all the changes should be permanent. It means that in case of any system failure, changes should not be lost. In simple words, When the Transaction is complete, the changes are saved.
Now we can understand this with the help of a Figure. This is very easy for us. This Figure is shown below.
![ACID in SQL Server]()
FAQs
Q. What is the difference between the Local and the Distributed SQL Server transactions?
The Local transaction is an SQL Server transaction that processes data from the local database server
The Distributed transaction is an SQL Server transaction that processes data from more than one database server
Q. Some transactions may not complete their execution successfully. Explain these transactions.
These transactions are known as,
Aborted.
Whenever a transaction finishes its final statement, it ultimately enters into this state:
Q. What is the SQL Server Transaction Log LSN?
Each Transaction log record that is written to the SQL Server transaction log file can be identified by its Log Sequence Number (LSN).