PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- HS-4985: _GetKeyId must not continue inside a doomed transaction (XACT_STATE() = -1): a re-select would return'
PRINT N'-- HS-4985: cleanly and defer error 3930 to the caller''s next write, masking the original error (deadlock etc.).'
PRINT N'-- HS-4985: Re-raise immediately with bare THROW so only the transaction owner can roll back and retry.'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Altering procedure [mig_item].[_GetKeyId]...'
GO
CREATE OR ALTER procedure [mig_item].[_GetKeyId]
	@businessEntityName varchar(50)
,	@keyValue nvarchar(1024)
,	@maxDeadlockRetries int
,	@keyId bigint output
as
	set nocount on

	declare @retryCount int = 0
	declare @startTranCount int = @@trancount;
	declare @localTransaction bit = 0;

	lblRetry:
	select	
		@keyId = [KeyId]
	from
		[mig_item].[MainKey]
	where
		[BusinessEntityName] = @businessEntityName
	and	[KeyValue] = @keyValue

	if @keyId is not null
		return

	-- Guarded insert: may race with other sessions inserting the same key
	begin try
		-- Try insert first

		-- Only manage a transaction if there wasn't one already
		if 0 = @startTranCount
		begin
			begin transaction;
			set @localTransaction = 1;
		end

		insert [mig_item].[MainKey] (
			[BusinessEntityName] 
		,	[KeyValue]
		) 
		values (
			@businessEntityName 
		,	@keyValue 
		)
		
		set @keyId = scope_identity()

		-- Normalize key fields for faster searches
		insert [mig_item].[MainKeyField] (
			[KeyId]
		,	[KeyName]
		,	[KeyValue]
		)
		select
			@keyId
		,	k.N.value('local-name(.)', 'nvarchar(256)')
		,	k.N.value('(@value)[1]', 'nvarchar(512)')
		from
			(select cast(@keyValue as xml) as keyXml) as x
			cross apply x.keyXml.nodes('/*/*') as k(N);

		-- If we started a local transaction, commit it
		if @localTransaction = 1 and @@trancount > 0
			commit transaction;

		return
	end try
	begin catch
		-- Insert failed

		-- If we started a local transaction, roll it back
		if @localTransaction = 1 and @@trancount > 0
			rollback transaction;

		declare @err int = error_number()

		-- HS-4985: a doomed caller transaction cannot be recovered here: reads
		-- still succeed, so the re-select path would return cleanly and defer a
		-- 3930 to the caller's next write, masking the original error. Re-raise
		-- immediately; only the transaction owner can roll back and retry. (A
		-- doomed local transaction was already rolled back above, so
		-- xact_state() is 0 there and retrying remains legitimate.)
		if xact_state() = -1
			throw;

		-- 2601 Duplicate in unique index for BusinessEntityName and KeyValue
		-- 2627 Unique constraint violation
		if @err in (2601, 2627)
		begin
			-- Another session inserted the same key between our SELECT and INSERT
			-- Go back and do the select again
			goto lblRetry
		end
		-- 1205: Transaction (Process ID %d) was deadlocked on %.*ls resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
		-- 1222: Lock request time out period exceeded.
		else if @err in (1205, 1222)
		begin
			if @retryCount < @maxDeadlockRetries 
			begin
				declare @delay time = case @retryCount
					when 0 then '00:00:00.000'
					when 1 then '00:00:00.005'
					when 2 then '00:00:00.010'
					else '00:00:00.020'
				end

				waitfor delay @delay

				set @retryCount = @retryCount + 1

				goto lblRetry
			end
		end;

		-- Any other error or out of retries: Rethrow
		-- Bare throw: raiserror cannot re-raise severity > 18 (HS-4981)
		throw;
	end catch
GO
