Transaction Isolation Levels

Define transaction and transaction isolation levels.

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
It locks data set until the transaction will be completed.

What is Isolation Levels?

Isolation keeps the transactions of multiple users isolated from each other. Transaction isolation level controls the degree of locking which occurs when selecting data.

This is necessary to avoid situations like:
- Lost updates- when two transactions read the same data.
- Dirty read- Occurs when a transaction reads data that has not been updated.
- Non repeatable reads- occur when different results are read multiple times.
- Phantoms- Occurs when row data matches the first time but does not match subsequent times.

Define transaction and transaction isolation levels.

A transaction may be defined as a collection of SQL statements, which collectively form a single unit of work.
Transaction isolation levels are used to control locking behavior of all SQL Server SELECT statements issued by a connection.

READ COMMITTED: Shared locks are held while any data is being read.

READ UNCOMMITTED: Specifies isolation level 0 locking. There are thus no shared locks or exclusive locks. Lease restrictive of all the isolation levels.

REPEATABLE READ: Locks are applied on all data being used by a query. However, new phantom rows can be inserted into the data set by another user and are included in later reads within the current transaction.

SERIALIZABLE: Issues a range lock on data set, preventing other users to update or insert data into dataset until the transaction is complete.

E.g.: