SQL Server Notification Services Question:
Download Questions PDF

Define transaction and transaction isolation levels?



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.

Transaction ensures either modification is committed or not committed.

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

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.

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
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

Download SQL Notification Services Interview Questions And Answers PDF

Previous QuestionNext Question
Explain OPENQUERY function and OPENROWSET function?What is Isolation Levels?