Пока как решения на ум пришло использование максимальной блокировке транзакции

Как то так:

DECLARE @maxId int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    BEGIN TRANSACTION ins
    	SET @maxId = (SELECT ISNULL(MAX([some_id]),0) + 1
    			FROM [mapping]
    			WHERE [user_id] = @user_id)

    	INSERT INTO [mapping] 
    		([some_id]
    		,[user_id]) 
    	VALUES 
    		(@maxId
    		,@user_id) 
    	SELECT	@id = SCOPE_IDENTITY()
    COMMIT TRANSACTION ins
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

и затем полученный id успользуется при вставке пар ключ->значение в другую таблицу