Post reply

Considering READPAST Hint for DELETE Operations – Lock Contention Analysis

  • August 20, 2025 at 7:21 am

    #4637996

    Salut l'équipe,

    Lors de l'exécution parallèle d'opérations DELETE sur la table [YourTableName], j'ai observé d'importants conflits de verrouillage, notamment de type d'attente LCK_M_U. Ces conflits semblent être causés par plusieurs sessions tentant de supprimer des lignes simultanément.

    J'envisage d'utiliser l'astuce de table READPAST pour potentiellement réduire le blocage en ignorant les lignes actuellement verrouillées par d'autres transactions. Voici un exemple :

    SUPPRIMER RDO  
                DE dbo.REF_DOS COMME RDO AVEC (READPAST)
                     JOINTURE INTERNE #DossierRéférence AS RD ON RD.COD_EXP = RDO.COD_EXP  
                                                           ET RD.COD_CEL = RDO.COD_CEL  
                                                           ET RD.NUM_DOS = RDO.NUM_DOS  
                     JOINTURE INTERNE dbo.REFERENCE AS RF ON RF.COD_TRA = RDO.COD_TRA  
                                                       ET RF.COD_REF = RDO.COD_REF  
                OÙ RF.IND_TECH_REF = 0  
                      ET RDO.NUM_UC EST NULL  
                      ET RDO.NUM_UM EST NULL  
                      ET RDO.NUM_COS EST NUL  
                      ET RDO.COD_ANO EST NUL  
                      ET N'EXISTE PAS  
                (  
                    SÉLECTIONNEZ LE TOP 1 1  
                    DE #DossierDeRéférence COMME RD2  
                    OÙ RD2.COD_EXP = RDO.COD_EXP  
                          ET RD2.COD_CEL = RDO.COD_CEL  
                          ET RD2.NUM_DOS = RDO.NUM_DOS  
                          ET RD2.COD_TRA = RDO.COD_TRA  
                          ET RD2.COD_REF = RDO.COD_REF  
                )

    Avant de mettre cela en œuvre, j'aimerais avoir votre avis sur deux points :

    Pensez-vous que l'utilisation de READPAST dans ce contexte pourrait contribuer à atténuer la contention de verrouillage ?

    Quels sont les risques et effets secondaires potentiels ?

    • notamment en ce qui concerne la cohérence des données
    • Si certaines lignes sont ignorées parce qu'elles sont verrouillées,

      n'hésitez pas à partager vos idées ou expériences.

    Attachments:
    You must be logged in to view attached files.

  • August 21, 2025 at 8:10 am

    #4638555

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • August 21, 2025 at 8:35 pm

    #4638855

    Most people on this forum are English speakers, but I will do my best with google translate to try to help.

    If I am not mistaken, READPAST is not a valid query hint for a DELETE operation and it doesn't make sense anyways. If the row you are trying to delete is locked by another operation, READING PAST that row isn't going to help. You have to wait for the lock to be released on the row then you can continue the DELETE operation. SQL doesn't just ignore that row and come back later, it will wait until it is unlocked and then take out a lock on it so it can perform its delete.

    A better option would be to not have multiple transactions trying to delete the same row.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply