SELECT FOR UPDATE

  • Hi,

    I have a SP to get one record from a worker table and mark the record as processed.

    But since it's a SELECT statement that gets the data the rows aren't locked for reading.

    CREATE PROCEDURE [dbo].[sp_ATH_SyncEntity_Get]

    AS

    BEGIN

    DECLARE @Id BIGINT, @EntityID UNIQUEIDENTIFIER, @EntityType TINYINT, @EntityOperation TINYINT, @status TINYINT

    BEGIN TRY

    BEGIN TRANSACTION

    -- GET RECORD TO SYNCHRONIZE: MUST SYNC ORGANIZATIONS 1ST

    SELECT TOP 1 @Id = Id, @EntityID = EntityId, @EntityType = EntityType, @EntityOperation = EntityOperation FROM ATH_SyncEntities WHERE Status = 0 ORDER BY EntityType, ID

    IF @Id IS NOT NULL

    BEGIN

    -- IF THERE'S ANYTHING TO SYNC SETS ITS STATUS TO 1 (PROCESSING)

    UPDATE ATH_SyncEntities SET Status = 1 WHERE ID = @ID

    -- RETURNS THE INFORMATION TO SYNC

    SELECT @Id ID, @EntityID EntityID, @EntityType EntityType, @EntityOperation EntityOperation

    END

    COMMIT TRANSACTION

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION

    END CATCH

    END

    This works fine as long as there's only one process reading from the table.

    But when I started a 2nd process they both got the same records almost all the time, since there was no lock on the SELECT.

    I made a little research and found the WITH (UPDLOCK) statement.

    Is this the best way to do this or there's another way (a transaction level, ...)?

    Thanks,

    Pedro



    If you need to work better, try working less...

  • You may use output clause to output the affected rows back to client

    Smth like this.

    with u as (select top(1) * from ATH_SyncEntities where Status = 0 order by EntityType, ID)

    update u set Status = 1

    output inserted.Id, inserted.EntityId, inserted.EntityType, inserted.EntityOperation, inserted.Status

    btw, do you want to update only one id at a time...


    I am really sorry for my poor gramma. And I hope that value of my answers will outweigh the harm for your eyes.
    Blog: http://somewheresomehow.ru[/url]
    Twitter: @SomewereSomehow

  • UPDLOCK is probably what you want.

    Here's another way that I believe will also work (no transaction needed).

    CREATE PROCEDURE [dbo].[sp_ATH_SyncEntity_Get]

    AS

    BEGIN

    DECLARE @Id BIGINT, @EntityID UNIQUEIDENTIFIER, @EntityType TINYINT, @EntityOperation TINYINT, @status TINYINT

    -- IF THERE'S ANYTHING TO SYNC SETS ITS STATUS TO 1 (PROCESSING)

    UPDATE ATH

    SET Status = 1

    ,@ID = ID

    ,@EntityID = EntityID

    ,@EntityType = EntityType

    ,@EntityOperation = EntityOperation

    FROM ATH_SyncEntities ATH

    INNER JOIN (

    SELECT Entity_type, ID

    ,rn=ROW_NUMBER() OVER (PARTITION BY EntityType, ID ORDER BY (SELECT NULL))

    ) b ON ATH.EntityType = b.EntityType AND ATH.ID = b.ID

    WHERE rn=1

    -- RETURNS THE INFORMATION TO SYNC

    SELECT @Id ID, @EntityID EntityID, @EntityType EntityType, @EntityOperation EntityOperation

    END


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • SomewhereSomehow (9/24/2012)


    You may use output clause to output the affected rows back to client

    Smth like this.

    with u as (select top(1) * from ATH_SyncEntities where Status = 0 order by EntityType, ID)

    update u set Status = 1

    output inserted.Id, inserted.EntityId, inserted.EntityType, inserted.EntityOperation, inserted.Status

    Thanks, works like a charm...

    btw, do you want to update only one id at a time...

    Yes, there's a trigger that whenever an entity is updated inserts a row on a sync entities table.

    There are some workers that read from that table an send the record to another database on another server.

    Each worker must only read an entity at a time..

    Thanks again,

    Pedro



    If you need to work better, try working less...

  • An alternative;

    UPDATE a SET [Status] = 1

    OUTPUT inserted.Id, inserted.EntityID, inserted.EntityType, inserted.EntityOperation

    FROM (SELECT TOP (1) Id, EntityID, EntityType, EntityOperation, [Status]

    FROM ATH_SyncEntities a

    WHERE [Status] = 0

    ORDER BY EntityType, ID) a

    Edit: What's the clustered index on table ATH_SyncEntities ?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • ChrisM@Work (9/24/2012)


    Edit: What's the clustered index on table ATH_SyncEntities ?

    Id column. It's a big int with identity seed.



    If you need to work better, try working less...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply