/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

Locking in Microsoft SQL Server. 
Part 18 - Key lookup deadlock

2013-06-11
*****************************************************/
set nocount on
go

use tempdb
go

if exists(
	select * 
	from sys.tables t join sys.schemas s on
		t.schema_id = s.schema_id
	where
		s.name = 'dbo' and t.name = 'Data'
)
	drop table dbo.Data
go

create table dbo.Data
(
	ClustKey int not null,
	Col1 int not null,
	NCIKey int not null,
	IncludedCol int not null,
	Placeholder char(8000),
)
go

create unique clustered index IDX_CI
on dbo.data(ClustKey)
go

create unique nonclustered index IDX_NCI
on dbo.data(NCIKey)
include(IncludedCol)
go


;WITH N1(C) AS (SELECT 0 UNION ALL SELECT 0) -- 2 rows
,N2(C) AS (SELECT 0 FROM N1 AS T1 CROSS JOIN N1 AS T2) -- 4 rows
,N3(C) AS (SELECT 0 FROM N2 AS T1 CROSS JOIN N2 AS T2) -- 16 rows
,N4(C) AS (SELECT 0 FROM N3 AS T1 CROSS JOIN N3 AS T2) -- 256 rows
,IDs(ID) AS (
	SELECT ROW_NUMBER() OVER 
		(ORDER BY (SELECT NULL)) FROM N4
)
insert into dbo.Data(ClustKey, Col1, NCIKey, IncludedCol) 
	select ID, ID, ID, ID
	from Ids
go

-- Session 1 code
declare
	@I int = 1

while 1 = 1
begin
	update dbo.data
	set IncludedCol = @I
	where ClustKey = 10
	
	select @I += 1
end
go

-- Session 2 code
declare
	@Col int

while 1 = 1
begin
	select @Col = Col1 
	from dbo.data
	where NCIKey = 10
end
go

-- Checking the locks
begin tran
	update dbo.data
	set Col1 = 0
	where ClustKey = 1
	
	select 
		l.request_session_id as [SPID]
		,object_name(p.object_id) as [Object]
		,i.name as [Index]
		,l.resource_type as [Lock Type]
		,l.resource_description as [Resource]
		,l.request_mode as [Mode]
		,l.request_status as [Status]
		,wt.blocking_session_id as [Blocked By]
	from 
		sys.dm_tran_locks l join sys.partitions p on
			p.hobt_id = l.resource_associated_entity_id
		join sys.indexes i on 
			p.object_id = i.object_id and 
			p.index_id = i.index_id	
		left outer join sys.dm_os_waiting_tasks wt on
			l.lock_owner_address = wt.resource_address and 
			l.request_status = 'WAIT'
	where 
		resource_type = 'KEY' and 
		request_session_id = @@SPID
commit
go

begin tran
	update dbo.data
	set IncludedCol = 0
	where ClustKey = 1
	
	select 
		l.request_session_id as [SPID]
		,object_name(p.object_id) as [Object]
		,i.name as [Index]
		,l.resource_type as [Lock Type]
		,l.resource_description as [Resource]
		,l.request_mode as [Mode]
		,l.request_status as [Status]
		,wt.blocking_session_id as [Blocked By]
	from 
		sys.dm_tran_locks l join sys.partitions p on
			p.hobt_id = l.resource_associated_entity_id
		join sys.indexes i on 
			p.object_id = i.object_id and 
			p.index_id = i.index_id	
		left outer join sys.dm_os_waiting_tasks wt on
			l.lock_owner_address = wt.resource_address and 
			l.request_status = 'WAIT'
	where 
		resource_type = 'KEY' and 
		request_session_id = @@SPID
commit
go

select 
	l.request_session_id as [SPID]
	,object_name(p.object_id) as [Object]
	,i.name as [Index]
	,l.resource_type as [Lock Type]
	,l.resource_description as [Resource]
	,l.request_mode as [Mode]
	,l.request_status as [Status]
	,wt.blocking_session_id as [Blocked By]
from 
	sys.dm_tran_locks l join sys.partitions p on
		p.hobt_id = l.resource_associated_entity_id
	join sys.indexes i on 
		p.object_id = i.object_id and 
		p.index_id = i.index_id	
	left outer join sys.dm_os_waiting_tasks wt on
		l.lock_owner_address = wt.resource_address and 
		l.request_status = 'WAIT'
where 
	resource_type = 'KEY'
go

--Code refactoring
declare
	@Col int
	,@ClustKey int
    
while 1 = 1
begin
	select @ClustKey = ClustKey
	from dbo.data
	where NCIKey = 10

	select @Col = Col1 
	from dbo.data
	where ClustKey = @ClustKey
end
go
