1
\$\begingroup\$

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
)
AS
BEGIN

DECLARE @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 TRY

BEGIN 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 CATCH
END

Like 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.

\$\endgroup\$
1
  • \$\begingroup\$ I retracted my close-vote and deleted my comment! Have a nice review! \$\endgroup\$ – Marc-Andre Apr 15 '16 at 19:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.