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