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

Locking in Microsoft SQL Server (Part 14) - 
	Deadlocks during DDL operations (alterations, partition switches, etc)
2012-06-12
*****************************************************/

set nocount on
go

create table dbo.Data
(
	Id bigint not null,
	Value bigint not null,
	Placeholder char(100) not null
		constraint DEF_Placeholder
		default 'Placeholder',
	
	constraint PK_Data
	primary key clustered(ID)
)	
go

insert into dbo.Data(Id, Value)
values(1,1),(2,2),(3,3),(4,4),(5,5)
go

-- Session 1
select @@SPID as [Session 1 SPID]
begin tran
	select * 
	from dbo.Data with (REPEATABLEREAD)
	where ID = 2

	select 
		resource_description
		,request_session_id as [SPID]
		,request_mode as [Mode]
		,request_type, request_status
	from sys.dm_tran_locks 
	where 
		resource_database_id = DB_ID() and
		resource_type = 'KEY'
-- commit

-- Session 2
select @@SPID as [Session 2 SPID]
begin tran
	update dbo.Data 
	set Value = -2
	where ID = 2
-- commit

-- Session 3
select @@SPID as [Session 3 SPID]
select * 
from dbo.Data with (READCOMMITTED)
where ID = 2

begin tran
	select * 
	from dbo.Data with (REPEATABLEREAD)
	where ID = 2
-- commit
go

create proc dbo.AlterDataTable
as
begin
	declare
		@ResCode int
		,@Sql nvarchar(max)

	begin tran
		-- obtaining exclusive application lock
		exec @ResCode = sp_getapplock
			@Resource = 'dbo.Data.Purge'
			,@LockMode = 'Exclusive'
			,@LockOwner = 'Transaction'
			,@LockTimeout = -1 -- no timeouts

		if @ResCode < 0 -- request failed
		begin
			-- alternatively we can use try/catch
			raiserror('Cannot obtain lock. Code %d',
				16,1,@ResCode)
			return
		end
	
		/* Put alteration code here */
		select @Sql = N'alter table dbo.Data 
add [Col' + convert(nvarchar(64),NEWID()) + N'] int'
		exec sp_executesql @Sql
					
		-- 10 seconds delay for demo purposes
		waitfor delay '00:00:10.000' 
	commit -- app lock has been released
end
go

create table dbo.DataTmp
(
	Id bigint not null,
	Value bigint not null,
	Placeholder char(100) not null,
	
	constraint PK_DataTmp
	primary key clustered(ID),

	constraint CHK_DataTmp_IsEmpty
	check(1=0)
)	
go

create proc dbo.ReadData
as
begin
	declare
		@ResCode int
		,@Sql nvarchar(max)

	begin tran
		-- obtaining exclusive application lock
		exec @ResCode = sp_getapplock
			@Resource = 'dbo.Data.Purge'
			,@LockMode = 'Shared'
			,@LockOwner = 'Transaction'
			,@LockTimeout = 3000 -- 3 seconds timeout

		if @ResCode < 0 -- request failed
		begin
			-- perhaps we need more granual control 
			-- of @ResCode
			select ID, Value from dbo.DataTmp
		end
		else begin
			select Id, Value from dbo.Data
			
			-- Releasing app lock 
			-- It's not required - app lock would 
			-- be released automatically when 
			-- transaction commits but can help
			-- if we need to have other activity 
			-- within the transaction
			exec sp_releaseapplock 
				@Resource = 'dbo.Data.Purge'
				,@LockOwner = 'Transaction'
		end		
	commit 
end
go

-- Session 1
exec dbo.AlterDataTable
select * from dbo.Data
go

-- Session 2 / 3
exec dbo.ReadData
