I'm in a situation where I need to manage a kind of FIFO process within a SQL procedure.  The procedure is called by a multithreaded windows application.I've created a table which look like this:+---------------+---------------+----+|     Field     |   DataType    | PK |+---------------+---------------+----+| TagName       | nvarchar(50)  | X  || LastProcessed | DateTime      |    |+---------------+---------------+----+Here's the code of my stored procedure (The isolation level is ReadCommitted)CREATE procedure [dbo].[SampleProc](    @p_Tagname nvarchar(50),    @p_Timestamp nvarchar(50),    @p_Value nvarchar(100),    @p_Quality float,    @o_Id bigint output,    @o_IsEvent bit output)ASBEGINDECLARE @Trancount int;DECLARE @TimeStamp DateTime;DECLARE @Meta_Data_Id int;DECLARE @TimeLocal datetime;DECLARE @PrevEventStatusId int;DECLARE @PrevEventId bigint;DECLARE @CalculatedEventCode nvarchar(250);DECLARE @Model int;DECLARE @Manufacturer int;DECLARE @EventStatusId int;DECLARE @ValueFloat float;DECLARE @AssetNo int;DECLARE @PrimaryStatus int;DECLARE @SecondaryStatus int;set @Trancount = @@TRANCOUNT;BEGIN TRY    if @Trancount = 0        begin tran;    else        save tran SampleProc;    --Lock here until other task for the same tag is committed or rollbacked    select @TimeStamp = LastProcessed from MyTable with (xlock,rowlock,serializable) where TagName = @p_Tagname    if (@TimeStamp is Null)        Insert into MyTable (TagName, Timestamp) values(@p_Tagname, @p_Timestamp)    else        Update MyTable set Timestamp = @p_Timestamp where TagName = @p_Tagname    set @o_Id = 0;    set @o_IsEvent = 0;    select @Meta_Data_Id = md.Meta_Data_ID, @o_IsEvent = case when de.ElementType_Code = 2 then 1 else 0 end,        @TimeLocal = case when @p_Timestamp between dc.UTC0_DST_Begins and dc.UTC0_DST_Ends then DATEADD(MI, dc.Offset_UTC_DST, @p_Timestamp) else DATEADD(MI, dc.Offset_UTC_ST, @p_Timestamp) end    from [DataWarehouse].[dbo].[Meta_Data] md    left join [DataWarehouse].[dbo].[Data_Element] de on (md.Element_Code = de.Element_Code)    inner join [DataWarehouse].[dbo].[Asset] a on (a.Asset_No = md.Asset_No)    inner join [DataWarehouse].[dbo].[Plant] p on (p.Plant_No = a.Plant_No)    inner join [DataWarehouse].[dbo].[Site] s on (s.Site_No = p.Site_No)    inner join [DataWarehouse].[dbo].[DSTCalendar] dc on (dc.TimeZone_Code = s.TimeZone_Code)    where md.Tag_No = @p_Tagname and dc.Year = Year(@p_Timestamp);    if nullif(@Meta_Data_Id, 0) is null    begin        set @o_Id = 0        set @o_IsEvent = 0        if @Trancount = 0            commit tran;        return;    end;    if @o_IsEvent = 1    begin        select @AssetNo = Asset_No from [DataWarehouse].[dbo].[Meta_Data] where Meta_Data_ID = @Meta_Data_Id;        select top 1 @PrevEventStatusId = [EventStatusId], @PrevEventId = [RealEventId]        from [Maintenance].[EventManagement].[RealEvent]        where AssetId = (select AssetId from [Maintenance].[MasterData].[Asset] where AssetNo = @AssetNo)        order by [EventStartTimeStamp_UTC0] desc;        select @Manufacturer = am.ManufacturerId, @Model = am.AssetModelId        from [Maintenance].[MasterData].[Asset] a        inner join [Maintenance].[MasterData].[AssetModel] am on (am.AssetModelId = a.AssetModelId)        where a.AssetNo = @AssetNo;        --Statut -999 -998 is for when quality is BAD        --Statut -999 -999 is for when the status doesn't exist in the master data        if (@p_Quality < 100)        begin            set @PrimaryStatus = -999            set @SecondaryStatus = -998        end        else        begin            --Retrive primary and secondary status from the merged value received (Always (Primary * 10000) + Secondary)            if (len(ltrim(rtrim(@p_Value))) < 5)                set @p_Value =  RIGHT('00000' + @p_Value, 5)            set @PrimaryStatus = cast(SUBSTRING(@p_Value, 1, len(@p_Value)-4) as int)            set @SecondaryStatus = cast(SUBSTRING(@p_Value, len(@p_Value)-3, 4) as int)        end        select @EventStatusId = EventStatusId         from [Maintenance].[MasterData].[EventStatus] e        inner join [Maintenance].[MasterData].[AssetModel] am on (e.AssetModelId = am.AssetModelId)        where am.ManufacturerId = @Manufacturer and am.AssetModelId = @Model and e.PrimaryStatus = @PrimaryStatus and isnull(e.SecondaryStatus, 0) = @SecondaryStatus        if @EventStatusId is null        begin            select @EventStatusId = EventStatusId             from [Maintenance].[MasterData].[EventStatus] e            inner join [Maintenance].[MasterData].[AssetModel] am on (e.AssetModelId = am.AssetModelId)            where am.ManufacturerId = @Manufacturer and am.AssetModelId = @Model and e.PrimaryStatus = -999 and e.SecondaryStatus = -999        end;        if (@PrevEventStatusId is null) OR (@EventStatusId <> @PrevEventStatusId)        begin            update [Maintenance].[EventManagement].[RealEvent]             set [EventEndTimeStamp] = @TimeLocal, [EventEndTimeStamp_UTC0] = @p_Timestamp, [EventDuration] = datediff(s, [EventStartTimeStamp_UTC0], @p_TimeStamp),                [LastUpdatedDate] = GetDate(), [LastUpdatedBy] = 'ProcessEventInputService'            where [RealEventId] = @PrevEventId;            set @o_Id = next value for [Maintenance].[EventManagement].[RealEvent_Seq];            insert into [Maintenance].[EventManagement].[RealEvent] (RealEventId, AssetId, EventStatusId, EventType, MgmtStatus, EventStartTimeStamp, EventStartTimeStamp_UTC0)            values (@o_Id, (select AssetId from [Maintenance].[MasterData].[Asset] where AssetNo = @AssetNo), @EventStatusId, 1, 1, @TimeLocal, @p_Timestamp)        end;    end    else    begin        if @p_Quality < 100        begin            set @o_Id = 0            --Commit only if we created the transaction            if @Trancount = 0                commit tran;            return;        end;        set @ValueFloat = TRY_CONVERT(float, replace(@p_value, ',', '.'));        -- 95% of the time will be an insert, try the insert and if it fails update        begin try            set @o_Id = next value for [DataWarehouse].[dbo].[Production_Detail_Seq]            insert into [DataWarehouse].dbo.Production_Detail (Id, Meta_Data_Id, Production_Detail_Time, Production_Detail_Time_Local, Production_Detail_Value, InsertedDate, ModifiedDate)            values (@o_Id, @Meta_Data_Id, @p_Timestamp, @TimeLocal, @ValueFloat, GETDATE(), GETDATE());        end try        begin catch            update [DataWarehouse].dbo.Production_Detail set @o_id = Id, Production_Detail_Value = @ValueFloat, ModifiedDate = GETDATE() where Meta_Data_Id = @Meta_Data_Id and Production_Detail_Time = @p_Timestamp        end catch    end;    --Commit only if we created the transaction    if @Trancount = 0        commit tran;END TRYBEGIN CATCH    DECLARE @Error int;    DECLARE @Message nvarchar(4000);    DECLARE @xstate int;    select @Error = ERROR_NUMBER(), @Message = ERROR_MESSAGE(), @xstate = XACT_STATE();    if @xstate = -1        rollback;    if @xstate = 1 and @Trancount = 0        rollback;    if @xstate = 1 and @Trancount > 0        rollback transaction SampleProc;    raiserror('SampleProc: %d: %s', 16, 1, @Error, @Message);END CATCHENDLike the comment says, what I want is the select to lock if a procedure is already proceding for the same tagname.  It works pretty well so far, but I'd like to know if there is a most efficient way to do this or if I may face some problem in the futur.
Exclusive row lock
sql;sql server;locking