Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I currently have the following stored procedure. However I am seeing some race conditions where I end up with incorrect data which I think is caused because the procedure isn't attomic and it can be called multiple times resulting in overlapping data.

CREATE PROCEDURE [dbo].[data_save]
    @user INT ,
    @players VARCHAR(MAX) ,
    @captains VARCHAR(MAX)
AS

-- Clear existing records if any    
DELETE FROM dbo.data WHERE user = @user

DECLARE @captainsTable TABLE (captainId INT);
INSERT INTO @captainsTable (captainId)
SELECT DISTINCT TOP 3 item FROM [platform].[SimpleSplit](@captains, ',')

INSERT INTO dbo.data
( user ,
  player ,
  iscaptain
)
SELECT @user
     , item -- this is what is returned from the SimpleSplit
     , CASE WHEN [item] in (select captainId from @captainsTable) THEN 1
         ELSE 0
       END
  FROM [platform].[SimpleSplit](@captains, ',')

I want to be able to execute this concurrently for different users but not for the same user.

The number of records for one user might be 4 at point 1 in time and then in point 2 the save might be executed with 6 records for instance. So just updating the existing records is not an option.

It's not a problem if the last call to the save procedure "wins" and overwirtes the previous ones but I don't want to end up with a combination of data from both. I am using sql server 2008.

share|improve this question

1 Answer 1

If your goal is to ensure that at the end of procedure dbo.data contains only records you inserted in the body of procedure, then you move DELETE to the very end (I assume you have primary key in data table, data_id:

--DELETE FROM dbo.data WHERE user = @user  -- do it at the end    
DECLARE @captainsTable TABLE (captainId INT);
DECLARE @newData TABLE (data_id INT);
INSERT INTO @captainsTable (captainId)
SELECT DISTINCT TOP 3 item FROM [platform].[SimpleSplit](@captains, ',')

--store inserted rows into table
INSERT INTO dbo.data
( user ,
  player ,
  iscaptain
)
OUTPUT inserted.data_id INTO @newData
SELECT @user
     , item -- this is what is returned from the SimpleSplit
     , CASE WHEN [item] in (select captainId from @captainsTable) THEN 1
         ELSE 0
       END
  FROM [platform].[SimpleSplit](@captains, ',');
--delete all except recently inserted
DELETE b FROM dbo.data b WHERE user = @user AND NOT EXISTS
(SELECT NULL FROM @newData a WHERE a.data_id = b.data_id);

Also, it makes sense to begin transaction (or create a savepoint if the sp executed withing the context of existing transaction) in the beginning of procedure , and commit at the end.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.