IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Suppression/Modification Trigger impossible =>Verrou? , delai de requete de verrou dépassé


Sujet :

Développement SQL Server

  1. #1
    Membre à l'essai
    Homme Profil pro
    Technicien Help Desk
    Inscrit en
    août 2015
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Technicien Help Desk

    Informations forums :
    Inscription : août 2015
    Messages : 30
    Points : 12
    Points
    12
    Par défaut Suppression/Modification Trigger impossible =>Verrou? , delai de requete de verrou dépassé
    Bonjour,
    J'ai un déclencheur sur une table dans un serveur SQL 2012. Quand j'essaie de modifier ce trigger (alter trigger ... ) ou même quand j'essaie de désactiver ce trigger, j'ai un message d'erreur "Delai de requete de verrou dépassé Erreur 1222".
    Je fais un Exec sp_lock, j'ai un ObjId qui m'interroge Type=TAB Mode)Sch-S... que puis je faire ? pour identifier la cause du pb ? pour libérer ce verrou ?

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 985
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 985
    Points : 49 809
    Points
    49 809
    Billets dans le blog
    1
    Par défaut
    Difficile de vous répondre sans voir le code de vos tables et du déclencheur.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Membre à l'essai
    Homme Profil pro
    Technicien Help Desk
    Inscrit en
    août 2015
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Technicien Help Desk

    Informations forums :
    Inscription : août 2015
    Messages : 30
    Points : 12
    Points
    12
    Par défaut
    C'est un trigger
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    ALTER TRIGGER [SYSADM].[ExportRL] 	ON  [SYSADM].[T_ITEM]
    FOR UPDATE						
    AS 	....
    qui intercepte les update de colonne
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    	IF update(ID_ITEM_ORG) 
    	BEGIN
    ....
    La table a beaucoup de champs (oui je sais c'est laid !)

  4. #4
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 985
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 985
    Points : 49 809
    Points
    49 809
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par eamrf Voir le message
    ....

    La table a beaucoup de champs (oui je sais c'est laid !)
    Et c'est sans doute la cause de tous vos problèmes !

    Commencez par modéliser correctement votre base et ce problème disparaitra !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  5. #5
    Membre à l'essai
    Homme Profil pro
    Technicien Help Desk
    Inscrit en
    août 2015
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Technicien Help Desk

    Informations forums :
    Inscription : août 2015
    Messages : 30
    Points : 12
    Points
    12
    Par défaut
    Oui c'est certain !!

    Un redemarrage du moteur peut-il libérer ce verrou ? en espérant qu'il ne réapparaisse pas ... plusieurs alter sur le declencheur ont été faits sans pb ...

  6. #6
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 985
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 985
    Points : 49 809
    Points
    49 809
    Billets dans le blog
    1
    Par défaut
    On ne redémarre JAMAIS un serveur SQL. C'est la pire des imbécilité !

    En aucune façon et pour quelques raisons que ce soit, une extinction des services SQL Server ou l’arrêt du serveur ou de la VM ne constitue une pratique acceptable.

    En effet, l’arrêt du serveur SQL entrainent les problématiques suivantes :
    1) Perte de la totalité des tables et des index mis en cache afin d’optimiser les accès aux données. Concrètement, toutes les données des tables et index devront être remontées du disque pour satisfaire les requêtes des utilisateurs, ce qui est 1000 à 10000 fois plus lent
    2) Perte de la totalité des plans d’exécution des requêtes mis en cache afin d’économiser le calcul des plans de requêtes. Concrètement cela signifie que les utilisateurs devront affronter des surcroits de temps d’exécution afin que l’optimiseur recalcule ces plans puisqu’ils ont été effacés du cache.
    3) Perte de la totalité des statistiques d’exécution des requêtes permettant de fournir un diagnostic de fonctionnement. Concrètement, cela ne permet plus d’investiguer dans ces statistiques et événements afin de savoir ce qui a pu se passer d’anormal dans le fonctionnement de SQL Server. De loin, ce problème est le plus critique qui soit :
    a. en matière d’optimisation il est nécessaire d’avoir un recul de fonctionnement continu d’au moins 1 mois, (3 à 6 serait mieux) pour mutualiser les opérations d’amélioration à effectuer (pose de nouveaux index, de vues indexées, de colonnes calculées, récriture de requêtes….)
    b. en matière de dysfonctionnement, ces statistiques permettent de comprendre ce qui a pu se passer afin de le corriger
    4) Perte de certaines transactions. Lorsque l’on arrête une instance SQL Server, toutes les transactions en cours sont invalidées et un processus de ROLLBACK sera entamé au redémarrage. Problème, ce mécanisme appelé « RECOVERY » (récupération en français) peut être long, voire très long par malchance… une transaction qui est par exemple restée ouverte pendant longtemps et a fait pas mal de modifications….). Or pendant la phase de récupération, la base est inaccessible à tout utilisateur jusqu’à complète finalisation du processus… Dans le cas invoqué, il n’est pas rare que vos utilisateurs attendent plusieurs heures avant de pouvoir accéder aux données !
    Comme vous n’êtes pas censé me croire, voici quelques textes qui parlent de la chose :
    https://dba.stackexchange.com/questi...er-speed-it-up
    https://www.sqlpassion.at/archive/20...s-a-good-idea/
    https://stevestedman.com/2016/09/reboot-sql-server/

    Il faut investiguer au moment du blocage via les DMV temps réel sys.dm_exec_... :
    • connections
    • sessions
    • requests

    Voir ce qui bloque et ne tuer que les processus fautifs bloquants !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  7. #7
    Membre à l'essai
    Homme Profil pro
    Technicien Help Desk
    Inscrit en
    août 2015
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Technicien Help Desk

    Informations forums :
    Inscription : août 2015
    Messages : 30
    Points : 12
    Points
    12
    Par défaut
    Merci pour ces précisions !! Je veux bien vous croire volontiers
    Je retiens ça pour les reboot trimestriels que fait faire l'équipe System pour les maintenances (patch etc).


    Pour les verrous, j'ai regardé ce que me donnait sp_lock....

    A priori les insertions, update et delete ne sont pas gênées par ce qui empêche de modifier/désactiver/supprimer les triggers.
    Une piste pour creuser et identifier ce qui coince? Connexions, sessions, requests? DMV?

  8. #8
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 985
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 985
    Points : 49 809
    Points
    49 809
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par eamrf Voir le message
    ...
    Une piste pour creuser et identifier ce qui coince? Connexions, sessions, requests? DMV?
    Il suffit de parcourir DVP...
    https://blog.developpez.com/sqlpro/p...stance-bloquee

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  9. #9
    Membre à l'essai
    Homme Profil pro
    Technicien Help Desk
    Inscrit en
    août 2015
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Technicien Help Desk

    Informations forums :
    Inscription : août 2015
    Messages : 30
    Points : 12
    Points
    12
    Par défaut
    merci de cette lecture. Très utile !

  10. #10
    Membre à l'essai
    Homme Profil pro
    Technicien Help Desk
    Inscrit en
    août 2015
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Technicien Help Desk

    Informations forums :
    Inscription : août 2015
    Messages : 30
    Points : 12
    Points
    12
    Par défaut
    J'ai exécuté la requete sus-cité dans le lien pour voir les LEADER_BLOCKER , je n'ai aucun résultat, donc a priori pas de verrous, mais toujours impossibilité de modifier ou désactiver le trigger pour cause de " Délai de requête dépassé - Erreur 1222". J'ai restauré un backup, j'arrive à désactiver le trigger..... bon j'ai pas de clients dessus, une certaine logique...

  11. #11
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 985
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 985
    Points : 49 809
    Points
    49 809
    Billets dans le blog
    1
    Par défaut
    Le délai de verrous dépassé est le time out d'avant exécution de la requête... en vu d'obtenir un verrou. Il n'y a pas de blocage !

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  12. #12
    Membre à l'essai
    Homme Profil pro
    Technicien Help Desk
    Inscrit en
    août 2015
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Technicien Help Desk

    Informations forums :
    Inscription : août 2015
    Messages : 30
    Points : 12
    Points
    12
    Par défaut
    Quelles seraient alors vos préconisations devant cette impossibilité d'obtenir un verrou?

  13. #13
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 985
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 985
    Points : 49 809
    Points
    49 809
    Billets dans le blog
    1
    Par défaut
    cela dépend de ce que vous voulez faire !
    Par exemple vous pouvez placer la base en mode mono utilisateur à votre profit pour effectuer une tâche particulière
    Vous pouvez aussi changer le mode général de verrouillage en utilisant le mode optimiste (snapshot).
    ...

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  14. #14
    Membre à l'essai
    Homme Profil pro
    Technicien Help Desk
    Inscrit en
    août 2015
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Technicien Help Desk

    Informations forums :
    Inscription : août 2015
    Messages : 30
    Points : 12
    Points
    12
    Par défaut
    Je recherche des infos sur le mode "optimiste" .... auriez-vous peut etre un lien bien documenté, je suis avec un SQL2012 ..... Est-ce jouable ?
    Sinon oui l'option "single user" sera ma solution.
    Merci pour vos retours

  15. #15
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 985
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 985
    Points : 49 809
    Points
    49 809
    Billets dans le blog
    1
    Par défaut
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER DATABASE ...<nom_base>... SET ALLOW_SNAPSHOT_ISOLATION ON;
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    ALTER DATABASE ...<nom_base>... SET READ_COMMITTED_SNAPSHOT ON;
    https://docs.microsoft.com/fr-fr/dot...-in-sql-server

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  16. #16
    Membre à l'essai
    Homme Profil pro
    Technicien Help Desk
    Inscrit en
    août 2015
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Technicien Help Desk

    Informations forums :
    Inscription : août 2015
    Messages : 30
    Points : 12
    Points
    12
    Par défaut
    Merci pour tout.
    Je crois que mes compétences en SQL se trouvent dépassés avec cette notion de "mode optimiste".

  17. #17
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 985
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 985
    Points : 49 809
    Points
    49 809
    Billets dans le blog
    1
    Par défaut
    Alors je vais vous l'expliquer... (extrait d'un de mes audits SQL Server)

    Verrouillage pessimiste vs optimiste

    Par défaut, SQL Server travail en mode de verrouillage pessimiste, c’est à dire que la pose de verrous bloquant les autres utilisateurs est préalable au démarrage d’une transaction (pour rappel une lecture – SELECT – comme toute commande SQL portant atteinte à la base – ALTER, CREATE, DROP, TRUNCATE, GRANT... – est une transaction).

    Autrement dit, SQL Server pose des verrous : partagés pour les lectures, et exclusif pour les écritures.

    C’est le mode pessimiste.

    Le mode optimiste, qui peut être activé dans SQL Server, consiste à prendre des copies de travail estampillée (marque « temporalo-transactionnelle ») pour effectuer les modifications, puis reporter ces modifications dans les données originales de la base. Cependant, ce report de modification final peut être perdu si, entre temps, d’autres utilisateurs ont modifié au moins une information parmi celles que l’on doit reporter. Pour cela, le système de gestion des transactions compare les valeurs des estampilles.

    Avantages et inconvénients :

    Nom : lock optimistic pessimistic.jpg
Affichages : 124
Taille : 53,0 Ko


    Activer le verrouillage optimiste

    Néanmoins, les serveurs possèdent aujourd’hui suffisamment de ressources pour que cette surconsommation d’espace (copie) et de temps, lié au verrouillage optimiste soit négligeable en regard des inconvénients des blocages du fait du mode pessimiste.

    Nous encourageons donc vivement, soit de :
    • autoriser le verrouillage optimiste (ce qui nécessite d’élever toute transaction au niveau d’isolation SNAPSHOT préalablement)
    • de forcer le verrouillage optimiste (ce qui place de facto toute transaction au niveau d’isolation SNAPSHOT)
    Ces deux options ne sont pas sans risque, car les résultats des traitements transactionnels peuvent différer. En effet dans le verrouillage pessimiste la base est par défaut en mode d’isolation READ COMMITTED (lecture de données validées) alors que dans le verrouillage optimiste, la base est automatiquement en mode d’isolation REPEATABLE READ (lecture répétables) ce qui peut changer les informations traitées si plusieurs lectures interviennent sur le même jeu de données.

    Autrement dit, seule une étude préalable, permet d’activer ce mode.

    Nous préférons d’ailleurs de loin que le mode de verrouillage optimiste soit établi une fois pour toute par forçage.

    NOTA : l’activation du mode de verrouillage optimiste permet de gagner beaucoup de temps, d’éviter de nombreux timeout et des interblocages (deadlocks) lorsque des requêtes très lourdes sont conjointes à une exploitation fortement transactionnelle due aux utilisateurs quotidiens...

    Il faudra faire attention à la base tempdb qui concentre les copies nécessaires à l’usage du mode de verrouillage optimiste de SQL Server.

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

Discussions similaires

  1. Réponses: 2
    Dernier message: 01/04/2007, 13h08
  2. Suppression de données impossible
    Par miwbath2007 dans le forum Bases de données
    Réponses: 2
    Dernier message: 14/03/2007, 21h28
  3. [MySQL] suppression-modification base via php
    Par mangamanga dans le forum PHP & Base de données
    Réponses: 5
    Dernier message: 16/06/2006, 22h52
  4. suppression de fichier impossible
    Par gdpasmini dans le forum C
    Réponses: 9
    Dernier message: 27/04/2006, 11h53
  5. suppression de fichier impossible (windows xp)
    Par annecyrond dans le forum Windows XP
    Réponses: 8
    Dernier message: 10/03/2005, 12h38

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo