Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SELECT FOR UPDATE Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 2:46 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:30 PM
Points: 515, Visits: 1,138
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...
Post #1363369
Posted Monday, September 24, 2012 3:36 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, June 27, 2014 4:17 AM
Points: 75, Visits: 443
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
Twitter: @SomewereSomehow
Post #1363382
Posted Monday, September 24, 2012 3:43 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1363385
Posted Monday, September 24, 2012 3:53 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:30 PM
Points: 515, Visits: 1,138
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...
Post #1363391
Posted Monday, September 24, 2012 6:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:02 AM
Points: 6,778, Visits: 13,974
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1363447
Posted Monday, September 24, 2012 7:03 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:30 PM
Points: 515, Visits: 1,138
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...
Post #1363467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse