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:
DELETE
pour le nettoyage du journal est en cours d'exécution? LCK_M_IX
et comment peut-il être résolu? 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).