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

Locking in Microsoft SQL Server. 
Part 17 - implementing critical sections in T-SQL

2013-05-08
*****************************************************/
set nocount on
go

if exists(
	select * 
	from sys.procedures p join sys.schemas s on
		p.schema_id = s.schema_id
	where
		s.name = 'dbo' and p.name = 'LoadRawData1'
)
	drop proc dbo.LoadRawData1
go

if exists(
	select * 
	from sys.procedures p join sys.schemas s on
		p.schema_id = s.schema_id
	where
		s.name = 'dbo' and p.name = 'LoadRawData2'
)
	drop proc dbo.LoadRawData2
go

if exists(
	select * 
	from sys.procedures p join sys.schemas s on
		p.schema_id = s.schema_id
	where
		s.name = 'dbo' and p.name = 'LoadRawData3'
)
	drop proc dbo.LoadRawData3
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 = 'LockTable'
)
	drop table dbo.LockTable
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 = 'RawData'
)
	drop table dbo.RawData
go

create table dbo.RawData
(
	ID int not null,
	ReadForProcessing datetime2(0) null,
	OtherColumns char(100) not null
		constraint DEF_RawData_OtherColumns
		default 'Placeholder for other columns',
	
	constraint PK_RawData
	primary key clustered(ID)
)
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.RawData(Id) 
	select ID
	from Ids
go

/* Example of the code */
declare
	@Result table
	(
		ID int not null, 
		OtherColumns char(100)
	)

;with DataPacket(ID,OtherColumns,ReadForProcessing)
as
(
	select top 10 ID,OtherColumns,ReadForProcessing
	from dbo.RawData
	where 
		-- assuming processing timeout is 1 min
		isnull(ReadForProcessing,'2001-01-01') < 
			dateadd(minute,-1,GetUtcDate())
	order by ID
)
update DataPacket
set
	ReadForProcessing = GETUTCDATE()
output inserted.ID, inserted.OtherColumns 
	into @Result(ID,OtherColumns)
	
select ID, OtherColumns
from @Result
order by ID
go


create table dbo.LockTable(Dummy int)
go

create proc dbo.LoadRawData1
as
begin
	set nocount on
	set xact_abort on
	
	declare
		@Dummy int
		,@Time1 datetime
		,@Time2 datetime

	declare
		@Result table
		(
			ID int not null, 
			OtherColumns char(100)
		)
		
	begin tran
		select @Time1 = GETDATE()
		select @Dummy = COUNT(*) 
		from dbo.LockTable with (TABLOCKX)
		select @Time2 = GETDATE()
		
		;with DataPacket(ID,OtherColumns,ReadForProcessing)
		as
		(
			select top 10 ID,OtherColumns,ReadForProcessing
			from dbo.RawData
			where 
				-- assuming processing timeout is 1 min
				isnull(ReadForProcessing,'2001-01-01') < 
					dateadd(minute,-1,GetUtcDate())
			order by ID
		)
		update DataPacket
		set
			ReadForProcessing = GETUTCDATE()
		output inserted.ID, inserted.OtherColumns 
			into @Result(ID,OtherColumns)
			
		-- artifitial delay for the testing
		waitfor delay '0:00:05.000'
	commit -- lock released
	select 
		@Time1 as [Before acquiring lock]
		,@Time2 as [Lock acquired]
		,GETDATE() as [Lock released]
	
	select ID, OtherColumns
	from @Result
	order by ID
end
go

exec dbo.LoadRawData1
go

create proc dbo.LoadRawData2
as
begin
	set nocount on
	set xact_abort on
	
	declare
		@ResCode int
		,@Time1 datetime
		,@Time2 datetime

	declare
		@Result table
		(
			ID int not null, 
			OtherColumns char(100)
		)
		
	begin tran
		select @Time1 = GETDATE()
		
		exec @ResCode = sp_getapplock
			@Resource = 'ReadRawData' -- the name of the lock
			,@LockMode = 'Exclusive' -- type of the lock
			,@LockOwner = 'Transaction' -- lock lives 
				-- for duration of the transaction
			,@LockTimeout = 20000 -- timeout in (ms)

		if @ResCode >= 0
		begin -- Success
			select @Time2 = GETDATE()
		
			;with DataPacket(ID,OtherColumns,ReadForProcessing)
			as
			(
				select top 10 ID,OtherColumns,ReadForProcessing
				from dbo.RawData
				where 
					-- assuming processing timeout is 1 min
					isnull(ReadForProcessing,'2001-01-01') < 
						dateadd(minute,-1,GetUtcDate())
				order by ID
			)
			update DataPacket
			set
				ReadForProcessing = GETUTCDATE()
			output inserted.ID, inserted.OtherColumns 
				into @Result(ID,OtherColumns)
				
			-- artifitial delay for the testing
			waitfor delay '0:00:05.000'
		end
	commit -- lock released
	select 
		@Time1 as [Before acquiring lock]
		,@Time2 as [Lock acquired]
		,GETDATE() as [Lock released]
	
	select ID, OtherColumns
	from @Result
	order by ID
end
go

exec dbo.LoadRawData2
go

;with DataPacket(ID,OtherColumns,ReadForProcessing)
as
(
	select top 10 ID,OtherColumns,ReadForProcessing
	from dbo.RawData with (updlock readpast)
	where 
		-- assuming processing timeout is 1 min
		isnull(ReadForProcessing,'2001-01-01') < 
			dateadd(minute,-1,GetUtcDate())
	order by ID
)
update DataPacket
set
	ReadForProcessing = GETUTCDATE()
output inserted.ID, inserted.OtherColumns 
	into @Result(ID,OtherColumns)
go


create proc dbo.LoadRawData3(@UseDelay bit)
as
begin
	set nocount on
	set xact_abort on
	
	declare
		@Dummy int

	declare
		@Result table
		(
			ID int not null, 
			OtherColumns char(100)
		)
		
	-- emulating race condition
	begin tran
		select @Dummy = COUNT(*) 
		from dbo.LockTable with (TABLOCKX)
		
		if @UseDelay = 1
			waitfor delay '0:00:05.000'
	commit
		
	;with DataPacket(ID,OtherColumns,ReadForProcessing)
	as
	(
		select top 100 ID,OtherColumns,ReadForProcessing
		from dbo.RawData with (updlock readpast)
		where 
			-- assuming processing timeout is 1 min
			isnull(ReadForProcessing,'2001-01-01') < 
				dateadd(minute,-1,GetUtcDate())
		order by ID
	)
	update DataPacket
	set
		ReadForProcessing = GETUTCDATE()
	output inserted.ID, inserted.OtherColumns 
		into @Result(ID,OtherColumns)
			
	select ID, OtherColumns
	from @Result
	order by ID
end
go

exec dbo.LoadRawData3 1
go
