How does SQL Server handle concurrent operations?

• Michael L John

  One Orange Chip

  Points: 27045

  More actions

  March 6, 2018 at 11:25 am

  #1982115

  Where are you controlling the transactions?  
  Are you using the TransactionScope class in c#?  If so, that may be the place to look.  
  I have seen far too many code blocks doing lots of other work, all wrapped in a TransactionScope.  The proc completes in milliseconds, but the code goes on and on.

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/

• junk.mail291276

  SSCrazy

  Points: 2503

  More actions

  March 6, 2018 at 12:36 pm

  #1982134

  Michael L John - Tuesday, March 6, 2018 11:25 AM

  Where are you controlling the transactions?  
  Are you using the TransactionScope class in c#?  If so, that may be the place to look.  

  No TransactionScope class, just auto-generated Entity Framework code:
    public virtual int DeleteProjectData(Nullable<int> projectId, string deleteType, string username)
   {
    var projectIdParameter = projectId.HasValue ?
      new ObjectParameter("projectId", projectId) :
      new ObjectParameter("projectId", typeof(int));
 
    var deleteTypeParameter = deleteType != null ?
      new ObjectParameter("deleteType", deleteType) :
      new ObjectParameter("deleteType", typeof(string));
 
    var usernameParameter = username != null ?
      new ObjectParameter("username", username) :
      new ObjectParameter("username", typeof(string));
 
    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("DeleteProjectData", projectIdParameter, deleteTypeParameter, usernameParameter);
   }

  I also switched to an older stored procedure that doesn't have cursors:
  
ALTER PROCEDURE [dbo].[DeleteProjectData]
  @projectId INT,
  @deleteType VARCHAR(10),
    @username NVARCHAR(255)
AS

    SET NOCOUNT ON

    BEGIN TRY

    DECLARE @realProjectId  INT = NULL,
    @isTemplate   BIT,
    @ErrorMessage   NVARCHAR(4000),
    @deleteTypeAll  VARCHAR(10),
    @deleteTypeNodes  VARCHAR(10)

      DECLARE @usernameBinary VARBINARY(255)
    SET @usernameBinary = CAST(@username AS VARBINARY(255))
    SET CONTEXT_INFO @usernameBinary

    SELECT @deleteTypeAll = 'All',
    @deleteTypeNodes = 'Nodes'
   
  -- ensure the input project exists and is not a template
  SELECT @realProjectId = ProjectId
  FROM Project
  WHERE ProjectId = @projectId

    IF @deleteType NOT IN (@deleteTypeAll, @deleteTypeNodes)
   BEGIN
   -- create a the error message for a project that does not exist    
   SELECT @ErrorMessage = N'The input Delete Type is not valid. Valid values are: ''' + @deleteTypeAll
           + ''' and ''' + @deleteTypeNodes + ''''
   
   -- raise the error
   RAISERROR (@ErrorMessage, 16, 1)

     END

    IF @realProjectId IS NULL
   BEGIN
   -- create a the error message for a project that does not exist    
   SELECT @ErrorMessage = N'The input ProjectId does not have a corresponding Project record. No calculations will be performed. ProjectId: ' + CAST(@projectId AS VARCHAR)
   
   -- raise the error
   RAISERROR (@ErrorMessage, 16, 1)

     END

    BEGIN TRANSACTION

      -- If deleting all, delete all reports associated with project first:
    IF @deleteType = @deleteTypeAll
        BEGIN

          DELETE [dbo].[ReportComment]
        WHERE ReportId in (SELECT ReportId
        FROM [dbo].[Report]
        WHERE ProjectId = @realProjectId)

          DELETE [dbo].[ReportMetric]
        WHERE ReportId in (SELECT ReportId
        FROM [dbo].[Report]
        WHERE ProjectId = @realProjectId)

          DELETE [dbo].[ReportTopN]
        WHERE ReportId in (SELECT ReportId
        FROM [dbo].[Report]
        WHERE ProjectId = @realProjectId)

          DELETE [dbo].[Report]
        WHERE ReportId in (SELECT ReportId
        FROM [dbo].[Report]
        WHERE ProjectId = @realProjectId)

          END
    ELSE
        BEGIN

          DELETE [dbo].[ReportTopN]
        WHERE ReportId in (SELECT ReportId
        FROM [dbo].[Report]
        WHERE ProjectId = @realProjectId)

          END
    
  IF @deleteType IN (@deleteTypeAll, @deleteTypeNodes)
   BEGIN
   DECLARE @Keys TABLE (
    ProjectId INT,
    NodeId INT,
    DeviationId INT,
    CauseId INT,
    ConsequenceId INT,
    SafeguardId INT,
    RecommendationId INT,
    RemarkId INT,
    DrawingId INT,
            RiskDataId INT,
            BowtieLoopId INT,
            BowtieId INT)

     -- build a list of keys associated with the project (simplifies delete queries)
   INSERT INTO @Keys (ProjectId,
         NodeId,
         DeviationId,
         CauseId,
         ConsequenceId,
         SafeguardId,
         RecommendationId,
         RemarkId,
         DrawingId,
                         RiskDataId,
                         BowtieLoopId,
                         BowtieId)
    SELECT p.ProjectId,
       n.NodeId,
       d.DeviationId,
       ca.CauseId,                 
       co.ConsequenceId,
       s.SafeguardId,
       r.RecommendationId,
       re.RemarkId,
       dr.DrawingId,
                 rd.RiskDataId,
                 bl.BowtieLoopId,
                 b.BowtieId
    FROM Project p
      LEFT OUTER JOIN Node n
       ON p.ProjectId = n.ProjectId
      LEFT OUTER JOIN Deviation d
       ON n.NodeId = d.NodeId
      LEFT OUTER JOIN Cause ca
       ON d.DeviationId = ca.DeviationId
      LEFT OUTER JOIN Consequence co
       ON ca.CauseId = co.CauseId
      LEFT OUTER JOIN Safeguard s
       ON co.ConsequenceId = s.ConsequenceId
      LEFT OUTER JOIN Recommendation r
       ON co.ConsequenceId = r.ConsequenceId
      LEFT OUTER JOIN Remark re
       ON co.ConsequenceId = re.ConsequenceId
      LEFT OUTER JOIN Drawing dr
       ON r.RecommendationId = dr.RecommendationId
                 LEFT OUTER JOIN Bowtie b
                    ON b.ProjectId = p.ProjectId
                 LEFT OUTER JOIN BowtieLoop bl
                    ON bl.BowtieId = b.BowtieId
                 LEFT Outer JOIN RiskData rd
                    ON rd.BowtieLoopId = bl.BowtieLoopId

      WHERE p.ProjectId = @realProjectId
    
   -- delete the data that was imported

     DELETE FROM Drawing
   WHERE DrawingId IN (SELECT DISTINCT DrawingId
          FROM @Keys)

     DELETE FROM Recommendation
   WHERE RecommendationId IN (SELECT DISTINCT RecommendationId
            FROM @Keys)
  
   DELETE FROM Safeguard
   WHERE SafeguardId IN (SELECT DISTINCT SafeguardId
          FROM @Keys)
          
   DELETE FROM Remark
   WHERE RemarkId IN (SELECT DISTINCT RemarkId
         FROM @Keys)
          
   DELETE FROM Consequence
   WHERE ConsequenceId IN (SELECT DISTINCT ConsequenceId
           FROM @Keys)

     DELETE FROM CauseToBowtieLoopDetails
        Where CauseId IN (Select Distinct CauseId
                      FROM @Keys)
               
   DELETE FROM Cause
   WHERE CauseId IN (SELECT DISTINCT CauseId
         FROM @Keys)        
        
   DELETE FROM Deviation
   WHERE DeviationId IN (SELECT DISTINCT DeviationId
          FROM @Keys)
  
   DELETE FROM Node
   WHERE NodeId IN (SELECT DISTINCT NodeId
         FROM @Keys)
  
        DELETE FROM RiskData
   WHERE RiskDataId IN (SELECT DISTINCT RiskDataId
         FROM @Keys)    
        
        DELETE FROM BowtieLoop
   WHERE BowtieLoopId IN (SELECT DISTINCT BowtieLoopId
         FROM @Keys)

          DELETE FROM BowtieToEquipmentLookup
        WHERE BowtieId IN (SELECT DISTINCT BowtieId
                            FROM @Keys)
                        
        DELETE FROM Bowtie
   WHERE BowtieId IN (SELECT DISTINCT BowtieId
         FROM @Keys)    

     DELETE FROM ProjectDeviation
   WHERE ProjectId = @realProjectId
  
   DELETE FROM ProjectSafeguard
   WHERE ProjectId = @realProjectId
  
   DELETE FROM ProjectRecommendation
   WHERE ProjectId = @realProjectId
  
   -- also delete from the metrics tables
  
   DELETE FROM ProjectMetrics
   WHERE ProjectId = @realProjectId
  
   DELETE FROM CauseMetrics
   WHERE ProjectId = @realProjectId

     DELETE FROM ConsequenceMetrics
   WHERE ProjectId = @realProjectId
  
   DELETE FROM RecommendationMetrics
   WHERE ProjectId = @realProjectId
  
   DELETE FROM SafeguardMetrics
   WHERE ProjectId = @realProjectId  
            
   END
  
  IF @deleteType = @deleteTypeAll
   BEGIN
  
   -- delete the project specific data (i.e., data not imported)
  
   DELETE FROM RiskMatrixAxis
   WHERE ProjectId = @realProjectId

     DELETE FROM SafeRecCategory
   WHERE ProjectId = @realProjectId

     DELETE FROM Participant
   WHERE ProjectId = @realProjectId

     DELETE FROM RiskRanking
   WHERE ProjectId = @realProjectId
  
   DELETE FROM Category
   WHERE ProjectId = @realProjectId
  
   DELETE FROM ImportFile
   WHERE ProjectId = @realProjectId
  
   DELETE FROM Project
   WHERE ProjectId = @realProjectId
  
   END

    COMMIT TRANSACTION

    END TRY

    BEGIN CATCH

     IF(@@TRANCOUNT > 0)
    BEGIN
    -- rollback all changes if any error occurred
    ROLLBACK TRANSACTION
    END
    
    -- raise the original error
   EXEC RethrowError;
  END CATCH