Transaction Isolation Levels Samples-1

Let create a database and a table for testing isolation levels.

use master go DROP DATABASE IF EXISTS IsolationLevelDB; GO ---- create a database CREATE DATABASE [IsolationLevelDB]; go use IsolationLevelDB go --- And we create a table then we insert some records create table IsolationLevelTable( id int identity(1,1) primary key, name    varchar(50) )  declare @i int = 0 while @i<10        begin        insert into IsolationLevelTable values('Isolation Level Test  Record'+convert(varchar(2),@i))             set @i = @i+1       end

READ UNCOMMITTED

READ UNCOMMITTED isolation level allows a transaction to read data from
the data or index page, regardless of whether a transaction is committed.

Firstly make a connection then start a transaction but should not commit it.

use IsolationLevelDB go begin tran update IsolationLevelTable set name = 'Isolation Level Test Record10' where id=10

First Connection

And we should make a new connection and we set read uncommited isolation level.

Then execute select query.

use IsolationLevelDB go set transaction isolation level read uncommitted select * from IsolationLevelTable

Second Connection

If you look at the query result set, you can see the uncommitted data.

But if you execute rollback command in the first connection screen then you
execute second connection select script again, it brings old record again. 

The result set is:

old record

READ COMMITTED

READ COMMITTED is Sql Server’s default isolation level.
If a transaction has made a change but it has no commit, read committed
isolation level doesn't allow another transaction for reading.

Let's do the same plan again. But we must make one change. 

Firstly, open a connection then execute below script: 

use IsolationLevelDB go begin tran update IsolationLevelTable set name = 'Isolation Level Test Record10' where id=10

First Connection

And then we should execute second script but we must change isolation level to
read committed like below:

use IsolationLevelDB go set transaction isolation level read committed select * from IsolationLevelTable

Second Connection

If you notice, there are no results, no messages.
Because second process is being blocked by first process.

If I execute sp_who2 procedure, I can see the blocking.
For this example first process’s session id is 66, second’s session id is 65. 

If you want to finish up the blocking, you can execute rollback or commit command in the
first connection. 

Different Scenario

Let’s execute below scripts step-by-step.

Open first connection:

----  first connection ----- first step in first connection use IsolationLevelDB; go set transaction isolation level read committed begin tran select * from [dbo].[IsolationLevelTable]

Open second connection (it is an update script) 

---- second connection  ---- second step use IsolationLevelDB; go update [dbo].[IsolationLevelTable] set name='Data Changed' where id=9 ---- We could updated record

Then we should turn back to first connection. Firstly we should execute third step.
And we will see that the record was changed.

----  first connection ----  first step in first connection use IsolationLevelDB; go set transaction isolation level read committed begin tran select * from [dbo].[IsolationLevelTable] ---- third step select * from [dbo].[IsolationLevelTable] ---- last step rollback;

The isolation level READ COMMITTED guarantees only that a transaction will not
read uncommitted data.