locked
SQL blocking on p_GrantInheritedAccess stored proc RRS feed

  • Question

  • We are keep on getting SQL blocking on our CRM SQL server, not sure what is going on? could not find anything in our plugins code, used everywhere no-locks in query expressions. checked POA table, looks good.

    Let me know if you have any idea what could cause this blocking. appreciate your help..

    FYI, We are On-Premise-Rollup 11

    blocker_text          :
                                  Create Proc p_GrantInheritedAccess
                                @ReferencingObjectId        uniqueidentifier,
                                @ReferencingObjectTypeCode     int,
                                @ReferencedObjectId        uniqueidentifier,
                                @ReferencedObjectTypeCode    int

                            As
                            Begin
                                SET NOCOUNT ON

                            --  For a new entity (Create scenario) ReferencingObjec
                            tId is not shared to any principal yet.
                            -- This check reduces by 50% cost of execution for crea
                            te scenario (no sharing for referencing object exists)
                            if (exists(select ObjectId from PrincipalObjectAccess p
                            oa1 with(NOLOCK) where poa1.ObjectId = @ReferencingObje
                            ctId AND poa1.ObjectTypeCode = @ReferencingObjectTypeCo
                            de))
                            Begin
                                -- Update Existing rows in POA that indicate that t
                            he referencing object was already
                                -- shared to the principals that have share access
                            to the referenced object

                                UPDATE poa1
                                SET InheritedAccessRightsMask = poa1.InheritedAcces
                            sRightsMask | poa2.AccessRightsMask | poa2.InheritedAcc
                            essRightsMask | 0x08000000
                                FROM PrincipalObjectAccess poa1 JOIN PrincipalObjec
                            tAccess poa2 ON (poa1.PrincipalId = poa2.PrincipalId)
                                WHERE poa1.ObjectId = @ReferencingObjectId
                                AND   poa1.ObjectTypeCode = @ReferencingObjectTypeC
                            ode
                                AND   poa2.ObjectId = @ReferencedObjectId
                                AND   poa2.ObjectTypeCode = @ReferencedObjectTypeCo
                            de
                            End

                            -- This check reduces by 50% cost of execution (no shar
                            ing for referenced object exists)
                            if (exists(select ObjectId from PrincipalObjectAccess w
                            ith(NOLOCK) where ObjectId = @ReferencedObjectId AND Ob
                            jectTypeCode = @ReferencedObjectTypeCode))
                            Begin
                                -- insert new rows for principals who were shared t
                            he referenced Object but not
                                -- the referencing Object

                                INSERT into PrincipalObjectAccess ( PrincipalId, Pr
                            incipalTypeCode, ObjectId, ObjectTypeCode, AccessRights
                            Mask, InheritedAccessRightsMask)
                                SELECT PrincipalId, PrincipalTypeCode, @Referencing
                            ObjectId, @ReferencingObjectTypeCode, 0, AccessRightsMa
                            sk | InheritedAccessRightsMask | 0x08000000
                                FROM PrincipalObjectAccess
                                WHERE PrincipalId NOT IN (SELECT PrincipalId FROM P
                            rincipalObjectAccess WHERE ObjectId = @ReferencingObjec
                            tId AND ObjectTypeCode = @ReferencingObjectTypeCode)
                                AND ObjectId = @ReferencedObjectId
                                AND ObjectTypeCode = @ReferencedObjectTypeCode
                            End

                            End
    block_program_name    : .Net SqlClient Data Provider                          

                                                                                                                                                       
    Wednesday, February 12, 2014 3:47 PM