MS SQL Server
  1. MS SQL Server
  3. Base de données SQL Server: requête INSERT bloquée avec le type d'attente LCK_M_IX
Intereting Posts
Exécutez sqlcmd sans avoir installé SQL Server save un résultat d'une fonction tsql dans une procédure stockée Trouvez le nombre d'loggings vides / vides pour toutes les colonnes TSQL Comparer le résultat de 2 requêtes Création d'une fonction de table CLR Erreur SQL Server: Pourquoi get l'erreur 26 lors d'une search de système de rôle dans .NET Comment installer l'application Windows et le server SQL sur la machine cliente? Paramètre de longueur non valide transmis à la bonne fonction après la conversion de MySQL vers SQL Server Résultats de requête SQL IN () générant des résultats indésirables Réplication transactionnelle bidirectionnelle SQL Server – Est-ce un bon cas d'utilisation? Comment faire pour configurer une application ASP.NET SaaS à plusieurs bases de données Supprime le jeu de résultats bidon de l'appel de procédure stockée nested Comment incrémenter la sum totale / nombre dans la ligne SQL Qu'est-ce que je fais de mal avec le support IN-clause de Dapper? Puis-je utiliser des guides de plan pour optimiser une requête à exécution lente?

Base de données SQL Server: requête INSERT bloquée avec le type d'attente LCK_M_IX

Notre application Java nettoie datatables de la table de journal via un travail cron. Voici la requête qui est exécutée pour le nettoyage: 

DELETE FROM AU_TRANSACTIONDATA WHERE AU_ACTIVITYENDTIME != 0 AND AU_ACTIVITYENDTIME <= 1464570001151

Nous avons un index sur la colonne AU_ACTIVITYENDTIME de ce tableau: 

 CREATE INDEX [IX_AU_TRANSDATA_ENDTIME] ON [AU_TRANSACTIONDATA]([AU_ACTIVITYENDTIME]) ON [PRIMARY];

Notre application sauvegarde datatables de transaction (générées lors de l'exécution des API dans notre application) dans cette table. Voici la requête INSERT: 

 INSERT INTO AU_TRANSACTIONDATA (AU_TRANSACTIONID, AU_TRANSACTIONNAME, AU_TRANSACTIONDOMAINID, AU_ACTIVITYNAME, AU_ACTIVITYID, AU_ACTIVITYPID, AU_ACTIVITYTYPE, AU_ACTIVITYSTARTTIME, AU_ACTIVITYENDTIME, AU_ACTIVITYSTATUS, AU_CORRECTDATA, AU_ERRORDATA, AU_USERID, AU_GROUPID, AU_NODENAME, AU_TRANSACTIONDESCRIPTION, AU_SEQUENCEID, AU_TRANSSEQUENCEID) VALUES (@P0, @P1, @P2, @P3, @P4, @P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13, @P14, @P15, @P16, @P17)

Nous sums confrontés à un problème lorsque le nettoyage du journal (requête DELETE ) et le traitement des données (requête INSERT ) se produisent simultanément. Nous avions environ 1,5 million d'loggings à nettoyer, donc la suppression de la requête prenait un certain time pour nettoyer les loggings du journal. Mais pendant ce time, le traitement a été bloqué et aucune requête INSERT n'a été INSERT .

Voici les journaux: 

 SPID at Head of Blocking Chain: SPID [ecid]: 3524 [0] Blocked by SPID: 0 Client Machine: xxxxx Client Process ID: 123 Application: jTDS Login Name: xxxx Last Batch: 5/30/2016 9:06:56 PM Wait Type: Wait Resource: Wait Time: 00:00:00 Database: Command Text: DELETE FROM AU_TRANSACTIONDATA WHERE AU_ACTIVITYENDTIME != 0 AND AU_ACTIVITYENDTIME <= 1464570001151 ---------------------------------------------------------------------- Blocked SPID: SPID [ecid]: 211 [0] Client Machine: xxxxx Client Process ID: 123 Application: jTDS Login Name: xxxxx Last Batch: 5/30/2016 9:06:56 PM Wait Type: LCK_M_IX Wait Resource: AU_TRANSACTIONDATA.IX_AU_TRANSDATA_ENDTIME Wait Time: 00:00:24 Database: xxxx Command Text: INSERT INTO AU_TRANSACTIONDATA (AU_TRANSACTIONID, AU_TRANSACTIONNAME, AU_TRANSACTIONDOMAINID, AU_ACTIVITYNAME, AU_ACTIVITYID, AU_ACTIVITYPID, AU_ACTIVITYTYPE, AU_ACTIVITYSTARTTIME, AU_ACTIVITYENDTIME, AU_ACTIVITYSTATUS, AU_CORRECTDATA, AU_ERRORDATA, AU_USERID, AU_GROUPID, AU_NODENAME, AU_TRANSACTIONDESCRIPTION, AU_SEQUENCEID, AU_TRANSSEQUENCEID) VALUES ( @P0 , @P1 , @P2 , @P3 , @P4 , @P5 , @P6 , @P7 , @P8 , @P9 , @P10 , @P11 , @P12 , @P13 , @P14 , @P15 , @P16 , @P17 )

Les journaux indiquent que les instructions INSERT attendent la ressource AU_TRANSACTIONDATA.IX_AU_TRANSDATA_ENDTIME (qui indique un index) avec le type d'attente LCK_M_IX. Dans certains journaux, nous avons vu wait resource être AU_TRANSACTIONDATA qui est la table elle-même.

Pouvez-vous s'il vous plaît expliquer ce qui suit:

  1. Pourquoi les requêtes INSERT sont bloquées lorsque la requête DELETE pour le nettoyage du journal est en cours d'exécution?
  2. Qu'entend-on par type d'attente LCK_M_IX et comment peut-il être résolu?
  3. La requête DELETE verrouille-t-elle la totalité de la table ou place-t-elle un verrou exclusif (X) sur l'index?

Je ne suis pas familier avec les stratégies d'attente et de locking dans SQL Server, donc toute aide à cet égard doit être appréciée.

EDIT : Nous avons déjà essayé de supprimer datatables en morceaux, c'est-à-dire 10000 lignes à la fois, mais cela n'a pas aidé la cause. Voici la nouvelle requête DELETE: 

 SET ROWCOUNT 10000 delete_more: DELETE FROM AU_TRANSACTIONDATA WHERE AU_ACTIVITYENDTIME != 0 AND AU_ACTIVITYENDTIME <= 1464570001151 IF @@ROWCOUNT > 0 GOTO delete_more SET ROWCOUNT 0

Si une session donnée essaie de faire quelque chose à la database qui nécessite des verrous exclusifs (comme INSERT , UPDATE , DELETE ) et dans une seule transaction, vous effectuez des opérations sur plus de 5000 lignes , SQL Server effectuera une escalade de verrous .

Au lieu de manipuler plus de 5000 verrous de niveau ligne individuels, il verrouillera exclusivement toute la table , donc aucune autre opération – pas même les requêtes SELECT – n'est possible jusqu'à ce que cette transaction ait été validée (ou annulée).