PRINT N'----------------------------------------------------------------------------------------------------------------------'
PRINT N'-- HS-4985: _GetKeyId must not continue inside a doomed transaction (XACT_STATE() = -1): retrying or returning'
PRINT N'-- HS-4985: cleanly defers error 3930 to the caller''s next write and masks the original error (deadlock etc.).'
PRINT N'-- HS-4985: catch now re-raises with bare THROW; the old lblError exit read error_message() outside the catch'
PRINT N'-- HS-4985: scope (NULL there) and re-raised nothing at all.'
PRINT N'----------------------------------------------------------------------------------------------------------------------'
GO

PRINT N'Altering procedure [mig_item].[_GetKeyId]...'
GO
CREATE OR ALTER procedure [mig_item].[_GetKeyId]
	@businessEntityName varchar(50)
,	@keyValue nvarchar(512)
,	@maxDeadlockRetries int
,	@keyId bigint output
as
	select
		@keyId = [KeyId]
	from
		[mig_item].[MainKey]
	where
		[BusinessEntityName] = @businessEntityName
	and	[KeyValue] = @keyValue
	
	if 0 = @@rowCount
	begin
		declare @retryCount int = 0
		declare @identity table([KeyId] bigint)

lblRetry:
		-- Guarded insert in case another thread has inserted the key after the select above and the insert now
		begin try
			-- Try insert first
			insert [mig_item].[mainKey] (
				[BusinessEntityName] 
			,	[KeyValue]
			) 
			output inserted.[KeyId] into @identity
			values (
				@businessEntityName 
			,	@keyValue 
			)
		
			select @keyId = [KeyId] from @identity
		end try
		begin catch
			-- Insert failed
			declare @err int = error_number();

			-- HS-4985: a doomed transaction (e.g. this session picked as deadlock victim
			-- inside the caller's transaction) cannot be recovered here: any further write
			-- raises 3930, and returning cleanly only defers the 3930 to the caller's next
			-- write, masking the original error. Re-raise it immediately; only the
			-- transaction owner can roll back and retry.
			if xact_state() = -1
				throw;

			if 2601 = @err
			begin
				-- 2601 Duplicate in unique index for BusinessEntityName and KeyValue
				-- Another thread inserted the key between the select and the insert
				select
					@keyId = [KeyId]
				from
					[mig_item].[MainKey]
				where
						[BusinessEntityName] = @businessEntityName
				and	[KeyValue] = @keyValue
			end
			else if @err in (1205, 1222)
			begin
				-- 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.
				set @retryCount = @retryCount + 1;

				if @retryCount > @maxDeadlockRetries
					throw;

				waitfor delay '00:00:00.1'
				delete from @identity
				goto lblRetry
			end
			else
			begin
				-- Any other error: re-raise with the original number, severity and state.
				-- Bare throw: raiserror cannot re-raise severity > 18 (HS-4981), and the old
				-- lblError exit read error_message() outside the catch scope, where it is
				-- NULL, so it re-raised nothing and execution continued (HS-4985).
				throw;
			end
		end catch

	end

	return
GO


