Transaction

A transaction is a set of operations that works as a single unit. The transactions can be categorized into explicit, autocommit, and implicit transactions. Every transaction should follow four properties called the ACID properties i.e. atomicity, consistency, isolation, and durability.

Atomicity

Transaction ensures either modification is committed or not committed.

Consistency

The data should be in consistent state when transaction process is completed. This means that all related tables are updated.

Isolation

SQL server supports concurrency when mean that data can be access or shared by many users. A transaction works in isolation and doesn’t allow other transaction to work concurrently on the same piece of data.

Durability

Data is permanent once transaction is completed and it can be recovered even if system fails.

There are four transaction isolation levels:

Read uncommitted

Read committed

Repeatable read

Serializable

Read uncommitted isolation levels

This is the lowest isolation level which can also be called as dirty read. Using this, you can read uncommitted data which can be rolled back at any point. With this level, SQL server uses share lock while reading data.

Read committed isolation levels

With this level, uncommitted data can’t be read. This is default isolation level and uses shared lock while reading data.

Repeatable read isolation levels

It locks all the data that is used in the query.

Serializable isolation levels