Search This Blog

Wednesday, December 23, 2009

All about locking , blocking and deadlocking ....Part 3

Isolation Levels

Read uncommitted
An isolation level where Dirty Reads , non-Repeatable reads and Phantom reads are possible .
least amount of blocking.

Read committed
An isolation level where non-Repeatable reads and Phantom reads are possible .
Reades will block writers only until the read is going on .But writers will block readers

Repeatable read
An isolation level where Phantom reads are possible .

Serializable
An isolation level where Dirty Reads , Repeatable reads and Phantom reads are not possible .

Snapshot
This Isolation keeps the previous committed copy of the row in tempdb and hence avoids blocking .It does no Dirty reads , Phantom reads or non-repeatable reads.
I will not be adding any Demos for this Isolation level .You can use all the demos give so far and in future .The only points you need to remember are :

-> before setting the isolation level to snapshot do
alter database deadlocktest set allow_snapshot_isolation on
go

-> Check the snapshots and rew version using these DMVs
sys.dm_tran_active_snapshot_database_transactions
sys.dm_tran_active_transactions
sys.dm_tran_current_transaction
sys.dm_tran_transactions_snapshot


Demo : Dirty Read
I hope you have all the Tables with you that we have created before .
If not , recreate them and we are ready to go :

1st Window :

set transaction isolation level read uncommitted
go
begin tran
update t1 set c1 = c1+5
--rollback

2nd Window :
set transaction isolation level read uncommitted
go
begin tran
select * from t1
--rollback

There is no blocking and the data that is not committed was read in window 2.This is called as Dirty read where an uncommitted data can be read.
This is only possible in read uncommitted .Try changing the isolation level to read committed and you will see blocking .



Demo : Non-repeatable Read
Since its possible in read-committed isolation level as well , I am usihg it instead of read - uncommitted isolation level .

1st Window
set transaction isolation level read committed
go
begin tran
select * from t1 where c1=240
--rollback

2nd Window
set transaction isolation level read committed
go
begin tran
update t1 set c1=240 where c1>200
commit

3rd Window
begin tran
select * from t1 where c1=240
--rollback


I know your confusion and anger .You might say I am stupid and its obvious that once the data is committed we can see its committed values :) ...
But lets imagine this :

-> The select in the first window is on millions of rows and is still running .
-> The update in the second row quickly comppleted in few mili seconds .
-> The third window started another select .
-> Finally both 1st and 3rd transactions finished .


The 1st Select * will see a different value and the second one will see a different one .

Am I right .........:D

But my question is Why the update transcation was allowed to change the data when there was a select on the whole table in the 1st window ??
If that update is blocked then I cannot see the changed values at all.
Why the update was not blocked even though I am doing a Select under begin tran and still not committed it ??
Why it looks like the S-lock is compatible with X-lock (selecy takes S lock)??

This is called as non-repeatable read .

The answer lies in the property of read-committed Isolation level.
In this Isolation level or in read-uncommitted isolation level , the S-locks are not kept till the transaction is rolledback or committed.
As soon as the read is over the lock exits .

And that is the reason you were able to update the rows .

Question
In one window you are doing select * from Table X under begin tran.
In another window you are doing Drop table X .
Will you be able to drop the table ??

I am sure you know the answer .Still try it yourself.
Also ,Try the above lab (where you are updating the rows to 240) in repeatable read or serializable mode.


Demo : Phantom reads
Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by another transaction. The transaction's first read of the range of rows shows a row that no longer exists in the second or succeeding read as a result of a deletion by a different transaction.Same is for insertion .

Imagine , if you are doing select * from customers table where date > 12/12/2012 and date < 15/12/2012
Another transaction inserted 200 more rows in this range after the first transaction read it (but the transaction is not yet committed).The third transaction also did a selsct and got 200 more rows ...


Be ready to get confused again ....but I will try my best not to get you confused ...

Lets create a small table

create table phantom (p1 int,p2 int)
go
insert into phantom values (1,2)
go 10
insert into phantom values (3,4)
go 10
insert into phantom values (5,6)
go 10
insert into phantom values (7,8)
go 10


Ist Window

set transaction isolation level repeatable read
go
begin tran
select * from phantom where p1 > 1 and p2 > 4
[remember this transaction is still running on millions of rows and trying to fetch the data]


2nd window
set transaction isolation level repeatable read
go
begin tran
insert into phantom values (5,8)
commit

[Assume here that the first transaction has already read the rows falling in to the range 1 and 4 and i s still running ....while second transaction committed very quickly.]

3rd window
set transaction isolation level repeatable read
go
begin tran
select * from phantom where p1 > 1 and p2 > 4


By the end when both 1st and 3rd trasnaction finishes there will be a difference of 1 row.
Try the same in serializable i.e. inserting the new row in 2nd window and see if you are allowed to do so :D .......
Fire me any question you have .........

Next we will discuss about Blocking and Deadlocking ...

No comments: