SQL Server Transaction - ACID Properties and Modes

A transaction is a logical unit of works in a database.


(1) A relational database transaction has ACID properties.

• Atomicity : All the changes in a transaction succeed or none of them succeed. There is no partially committed transaction. Either all changes committed or rolled back.
• Consistency: All transactions are performed in a way that the database can stay in a consistent state. 
• Isolation :  One transaction occurs in isolation from other transactions, and so the change made in the transaction is not visible to other transaction until it the transaction is committed.
• Durability : Databases ensure that all changes made by committed transactions are never lost by using logs that keep track of all the committed changes.

(2) Transaction Modes in SQL Servers

• Autocommit mode - a single DML and DDL statement is one transaction, and it will be automatically committed or rolled back  (default mode in SQL Server)
• Implicit transaction mode - SQL Sever automatically starts a transaction when we issue a DML or DDL statement. We must commit or roll back explicitly to end the transaction (default ANSI/ISO mode/default in Oracle)
• Explicit transaction mode - Begin a transaction with "BEGIN TRANSACTION" and end the transaction by commit or roll back.