September 17, 2008 at 2:40 am
Hi. I was just wondering if someone could give me some advice on the best way to do following
I have a stored procedure that when ran by a user checks for a value in a table.. if the value exists then the query continues as planeed however if the value does not exist an update to the table is ran. This update gets data from a linked server and is rather slow but necessary. Once the update is complete and the check is ran again... If the value still is not there the sproc exits gracefully else continues and finishes.
The advice i am seeking is on the topic of transactions or table locking (i think) If the stored procedure is ran by a user and is in the process of updating from the linked server... if the stored proc is then ran by another user.. how do i stop it from checking the table for the existing value until the 1st stored procedure has finished updating the table.
Here is a example of what i am working on
SELECT Value FROM TABLE1
WHERE Value = @Value
IF @@ROWCOUNT = 0
BEGIN
--Update Table1
EXEC s_UpdateTable1
SELECT Value FROM TABLE1
WHERE Value = @Value
IF @@ROWCOUNT > 0
BEGIN
SET @AvailRecords = 1
END
ELSE
BEGIN
SET @AvailRecords = 0
END
END
ELSE
BEGIN
SET @AvailRecords = 1
END
September 17, 2008 at 6:32 am
Maybe you can use the WITH (NOLOCK) hint in your SELECT statement. This will read the data even if an exclusive lock is set on the record.
:exclamation:But beware: a query with this hint can give you uncommitted data in return.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply