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

Games Port Play

Yesterday evening, I was winding up when my collegue came and said :

"I have a 2008 instance which when I connect to shows me the right insance name but the version is 3073 which is 2005 and the databases also belongs to 2005 instance" .
This means that , I invited you to my house #X, you followed a route and came to my house .But you found that its not my house and somebody else's house Y even though the house number is correct .How is that possible ...........

After resolving the issue I reproduced the issue for better understanding .

screenshot :

I have one 2000 instance , one 2005 instance and one 2008 instance showing below




I did something and here is the new screenshot :




Test 1:
-----------
Assuming that there might be some issue with SSMS / SQLWB (in short assuming management Studio got corrupted ), I connected wih the 2008 instance using SQLCMD



April is 2008 instance but its showing as 2000 instance .So Management studio is not corrupt and we need not uninstall it .


Test 2 :
-----------
Tried to connect to April using Named Pipe protocol instead of TCPIP



It was successful and showing the right version 10.0.1600.So there is no isue with the Named pipe query .




Test 3 :
----------
Found the port on which April is listening through registry and tried to connect using the port



and the result was :



It means that April is not listening on PORT 1414.Then on which port its listening ??
So I decided to create an alias to force April to listen on 1414 port .
I used Client Network utility (CLICONFG) to create the alias .
Guess what ..........there was already an alias which was meant for April but was pointing to port 1918 .
On 1918 SQL Server 2000 was listening ....[used netstat -abn] ......
Removed that alias and created the new one forcing April to listen on its own port 1414....
And it worked .......2005 also had the same issue ...

Not sure if its a Bug as its possible only from higher version to lower version and not from lower to higher version
i.e. its possible that SQL 2008 or 2005 listen on 2000 port but not vice versa ...

Let me log the bug and see what happens ........

Hence the mistry was resolved ...

Happy learning .....

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

Now , we know about :

Locks
Types of locks (S,U,X)
Lock compatibility with each other .

Before going further , let me breifly explain you about Intent locks.Mainly Its IS and IX .

Both Intent-Exclusive and Intent-Shared are used so that there should be no problem for Shared or Exclusive locks .
In other words the traffic police blocks the read if any big minister is about to come , fearing that the minister might get late or stopped if the the road is not blocked for public used .But the road will be actually used only when the minister's vehicle will come .

So the intent locks are just a way to ensure better concurrency in accessing the data .
For other types of locks please refer BOL or the links I referred in my previous post.

Lets move forward ....

Lock Escalation
-----------------
Each lock takes 96 bytes worth memory (lock manager gives it).So the more locks are there the more memory will be used ....

Under certain circumstances the Engine realizes that the resources are gettin gwasted and that it needs to change the several smaller level locks to fewer higher level locks.This is called as lock escalation .You can overrride by using locking hints (use BOL for locking hints )


these circumstances are :

1)
When a single Transact-SQL statement acquires 5,000 locks on a single table or index.

2)
When the number of locks in an instance of the Database Engine exceeds memory (60% of Max server memory) or configuration thresholds(using sp-configure 'locks' option).


Test :
--------
Lock escalation LAB
====================

Under sp_configure setting for 5000 locks
------------------------------------------

use master
go
sp_configure 'locks',5000
go
reconfigure with override
go


Restart the instance as this setting is non dynamic .Then copy the code below in one query window .
After creating the table run the first update statement unde begin tran and notice the number of locks using sp_locks.
Then rollback the tran and try with another update statement .You will notice that before the number of locks reaches 60% of 5000 lock escalation occurs.

USE [deadlocktest]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t1](
[c1] [int] NULL,
[c2] [int] NULL,
[c3] [int] NULL,
[c4] [char](5000) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
declare @val int
set @val = 2000
while @val >0
begin
insert into t1 values (@val,@val,@val,@val)
set @val=@val-1
end
go

update t1 set c2 = 1600 where c1 > 700 and c1 <1000
go
update t1 set c2 = 1650 where c1 > 900 and c1 <1200
go
update t1 set c2 = 1700 where c1 > 1100 and c1 <1400
go
update t1 set c2 = 1800 where c1 > 1300 and c1 <1600
go
update t1 set c2 = 1850 where c1 > 1500 and c1 <1800
go
update t1 set c2 = 1900 where c1 > 1700 and c1 <2000
go


begin tran
update dbo.t1 set c2 = 231 where c2 > 1800 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1700 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1655 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1654 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1650 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1600 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1550 and c3 > 700
sp_lock
rollback


Under sp_configure setting for 0 locks(which means maximum)
-----------------------------------------------------------

The table and data is same .Just run the update queries below as you were running above and roll them back after checking the number of locks.

begin tran
update dbo.t1 set c2 = 231 where c2 > 1800 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1700 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1655 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1600 and c3 > 700
--rollback
--sp_lock

begin tran
update dbo.t1 set c2 = 231 where c2 > 1550 and c3 > 700
--sp_lock
--rollback

begin tran
update dbo.t1 set c2 = 231 where c2 > 100 and c3 > 200
--rollback
--sp_lock


After knowing about locks , their compatibility and lock escalation lets talk about Isolation levels .
Broadly , there are 4 Isolation levels :

-Read Uncommitted
-Read committed
-Repeatable read
-Serializable

SQL Server 2005 introduced to more choices
-READ_COMMITTED_SNAPSHOT option for Read committed Isolation level
-Snapshot Isolation Level

We differentiate the Isolation levels on the basis of Dirty Reads , Non-repeatable reads and Phantom reads.
we need to know about them first .But if I start explaining them I will use these isolation levels again and you will be confused.
So I will be explaining these three reads as well as Isolation levels in parallel .
for theory purpose :

Dirty reads : We can read the uncommitted data
Repeatable read : A transaction Selects the data without committing >> B transaction is able to modify the data >> A transaction sees the changed data .
Phantom reads : the ability to insert the data by one transaction in to the range of values which is being read by other transaction .

Rest ...............in next blog ..

Happy learning ..

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

Locks
------
Locks are the objects that helps in access synchronization for objects like Schema , database , tables , extents ect .


Types of locks
---------------

Shared (S) locks
-----------------
allows us to read the data by running the select queries .During the time Shared lock exist no other transaction can modify the data being read .The good part it that these locks are released as soon as the select completes even though the transaction might still be running .But there is an exception .This does not hold true if the isolation level is Repeatable Read or serializable .Why ???? because the Shared locks are not released and are kept till the transaction commits or rolls back .


Test
-----

1st Qquery window
-------------------
Create table Shared_test(sh int)
go
declare @sht int
set @sht=50
while @sht >0
begin
Insert into shared_test values (@sht)
set @sht=@sht-1
end
go
begin tran
select * from shared_test


2nd Qquery window
------------------
sp_lock
go

/*you will notice that there is no lock on that object*/

drop table shared_test
go

/*The drop will be successful*/

Again 1st Qquery window
-----------------------
Rollback
go
set transaction isolation level repeatable read
go

Create table Shared_test(sh int)
go
declare @sht int
set @sht=50
while @sht >0
begin
Insert into shared_test values (@sht)
set @sht=@sht-1
end
go
begin tran
select * from shared_test


Again 2nd Qquery window
-------------------------
sp_lock
go

/*you will notice that there is no lock on that object*/

drop table shared_test
go


Result:
---------
You will see that there are a lot of shared locks on the RID (row identifier for each row)and the Drop will be blocked (try doing sp_lock from another window to see the SCH-M lock in wait state).

Compatibility
--------------
Shared locks are compatible only with Shared , Intent Shared and update locks.



Exclusive(X) locks
-------------------
Its the most strict lock .Unless you are in read-uncommitted isolation level or using NOLOCK hint you will be blocked...

Compatibility
---------------
Exclusive lock will only be compatible with SCH-S(Shared Schema) and RI-N(Insert Range Null) locks.


Update (U) locks
-----------------
Before understanding about the update locks let me tell you that whenever we update , insert or delete we do two operations : Searching where to insert update or delete and doing that action .

Seaching is done using Shared locks and completing the action of insert , update or delete will be done by the exclusive lock (both covered above).

Other important point that you have read above (search where (",)..) that in isolation level higher than read committed the shared locks are kept and not released till the end of transaction .So Imagine that there is no UPDATE locks and you and your friend both are updating x rows to a different values in the same table from 2 different client machines , at the same time and in isolation level repeatable read (we will learn about Isolation levels in sometime ) .I forgot to mention that the transaction is not committed.since the shared locks are compatible with shared locks , you both will be granted the shared locks ....fine..

Now , the X mode lock of A friend is not compatible with S mode lock of another friend named B ..so Blocking occure as X mode is not able to get converted to S mode .Similarly , the same will happen with B friend as well (I will leave it up to you to guess)...and this will cause deadlock..


Update lock will ensure that No two transactions can try to update the same resource at the same time ...further till the update resource is doing the searching it will show you as U in sp_lock .The moment it tries to modify the row , U mode disappear and X mode is shown.

Test :

I am sure you still have the same two queryanalyzer windows open with repeatable read isolation level.


1st Query Window
------------------
There is already a select going on .But for your understanding I am typing the same again :

begin tran
select * from shared_test


2nd Query Window
-----------------
If you have not yet stopped the previous transaction of dropping the table , then stop it .
run this command

begin tran
update Shared_test set sh=(sh*2) where sh > 35

It will be blocked for sure

1st Query Window
------------------
sp_lock
go


Result:
---------
among other locks, you will see(modified for better view) :

51 RID 1:18773:0 X CNVT
51 TAB IX GRANT
51 PAG 1:18773 IX GRANT
51 RID 1:18773:0 U GRANT
53 RID 1:18773:0 S GRANT
53 PAG 1:18773 IS GRANT



Here ,
18873 is the page id
1 is the datafile
0 is the row id


1)
SPID 53 took IS lock on page 18773 and then S lock on rowid 0 fearing if someone tries to modify the page should be blocked ....agreed

2)
SPID 51 took U lock row id 0 of page 18773 which is allowed as S and U locks are compatible.Remember internally its also S lock at this moment .The moment it tries to change something it will be converted to X lock and will no forther be U lock any more ---> 51 then moved ahead and before trying to take the X lock on the RID it first took the IX lock on the page (in case someone drops the object which its trying to modify)which is granted because IS and IX are compatible .

But when U lock on RID 1:18773:0 tried to convert in to X lock , It got blocked and you can see CNVT ..

But this test was not exactly the example that I gave when I was talking about the U locks

So lets try what i said ...2 updates on the same table and same rows ...


Test
-----
make sure you rollback all the transactions on the 2 QA windows and remove all the query text .

1st Window
------------
Set transaction isolation level repeatable read
go
begin tran
update Shared_test set sh=(sh*2) where sh > 35

2nd window
------------
Set transaction isolation level repeatable read
go
begin tran
update Shared_test set sh=(sh*2) where sh > 35

1st Window
------------
sp_lock


Results:
-----------

51 741577680 PAG 1:18773 IU GRANT
51 741577680 RID 1:18773:0 U WAIT
51 741577680 TAB IX GRANT
53 741577680 TAB IX GRANT
53 741577680 PAG 1:18773 IX GRANT
53 741577680 RID 1:18773:0 X GRANT


1)

53 took IX on page before X on rowid (I will explain later on why IX and IS are taken) which is right and then IX on table

2)

53 also took IX on table (IX and IX are compatible) and IU on page (IX and IU are compatible) .But U and X are not compatible and hence blocking.Had there been no U it would have been a deadlock ....


Like this do some testing on your own and gain some knowlwdge..


Referance :
Lock Compatibility (Database Engine):
http://msdn.microsoft.com/en-us/library/ms186396(SQL.90).aspx

SP_LOCK :
http://msdn.microsoft.com/en-us/library/aa238824(SQL.80).aspx

Happy learning !!!!!

Let me know what you would like me to post and research upon :)

Dear Blog Visitors ,

So far I have been blogging on the issues i have faced and research done related to SQL Server 2005 (not yet started 2008).

However , This might or might not interest you as the probability of you facing the same issue as I have faced is rare ..

So for the benefit of all , I am ready to assist anyone on the SQL Server issues or any kind of request ..

Please add your requests as comments or mail me at hi_abhay78@yahoo.co.in / 1978abhay@gmail.com

Happy learning ...