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.
