SELECT FOR UPDATE

Question

Post reply

SELECT FOR UPDATE

PiMané

SSChampion

Points: 13965
More actions
September 24, 2012 at 2:46 am

#268229

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

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

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

SomewhereSomehow SSCommitted Points: 1586 More actions · Answer 1

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

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

PiMané SSChampion Points: 13965 More actions · Answer 3

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

PiMané SSChampion Points: 13965 More actions · Answer 5

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