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.