Récemment, j'ai rencontré un scénario d'impasse sur Box OLTP (SQL Server 2005) d'un client et a constaté que c'était causé par deux procédures stockées appelées par 2 threads différents.
1, insérer SP qui insère des données dans la table x. P>
deadlock-list deadlock victim=process3c77d68 process-list process id=process3c12c58 taskpriority=0 logused=1044 waitresource=PAGE: 17:8:7726 waittime=1250 ownerId=5169682909 transactionname=user_transaction lasttranstarted=2011-02-03T03:34:03.443 XDES=0xfe64d78b0 lockMode=IX schedulerid=2 kpid=9544 status=suspended spid=219 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-02-03T03:34:03.457 lastbatchcompleted=2011-02-03T03:34:03.453 clientapp=.Net SqlClient Data Provider hostname=HQMTSRV026 hostpid=3308 loginname=EASUser isolationlevel=read committed (2) xactid=5169682909 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056 executionStack frame procname=adhoc line=1 stmtend=296 sqlhandle=0x0200000084ce2a1d0e95a5623fa3a9c0981d422e33cab999 (@1 int<c/>@2 varchar(8000)<c/>@3 nvarchar(4000))INSERT INTO [VB_Audit_TransactionDetail]([ItemID]<c/>[TransactionID]<c/>[ItemValue]) values(@1<c/>@2<c/>@3) frame procname=adhoc line=1 stmtend=296 sqlhandle=0x02000000afcb1733f435fb93e13556600acf32bb32e10020 Insert Into VB_Audit_TransactionDetail (ItemID <c/> TransactionID <c/> ItemValue ) Values (4 <c/> '0255978c-f56e-477e-b361-8abe62433cff' <c/> N'HQOLB006' ) frame procname=EAS.dbo.SP_Insert line=13 stmtstart=482 stmtend=522 sqlhandle=0x03001100805efa5997d69400719600000100000000000000 exec (@CommandText) inputbuf Proc [Database Id = 17 Object Id = 1509580416] process id=process3c77d68 taskpriority=0 logused=364 waitresource=PAGE: 17:6:334008 waittime=1234 ownerId=5169682116 transactionname=user_transaction lasttranstarted=2011-02-03T03:34:03.053 XDES=0xa8e297cd0 lockMode=X schedulerid=12 kpid=10300 status=suspended spid=327 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2011-02-03T03:33:41.137 lastbatchcompleted=2011-02-03T03:33:41.133 clientapp=Microsoft SQL Server hostname=HQSSISSRV002 hostpid=7632 loginname=NBKDOM\SQLCSRVC isolationlevel=read committed (2) xactid=5169682116 currentdb=17 lockTimeout=4294967295 clientoption1=671350816 clientoption2=128056 executionStack frame procname=EAS.dbo.PurgeAuditTransactionTables line=59 stmtstart=4202 stmtend=4728 sqlhandle=0x030011006354a2313d11ae00979a00000100000000000000 DELETE [dbo].[Audit_TransactionDetail] FROM [dbo].[Audit_TransactionDetail] T1 WITH (NOLOCK) INNER JOIN [dbo].[Audit_NBKTransaction] T2 WITH (NOLOCK)ON T1.[TransactionID] = T2.[TransactionID] WHERE TransactionPostedDateTime < @LastReplicationDateTime frame procname=adhoc line=1 sqlhandle=0x0100110096968c0560c430ff190000000000000000000000 EXEC PurgeAuditTransactionTables '02 Feb 2011 19:00:13:870' inputbuf EXEC PurgeAuditTransactionTables '02 Feb 2011 19:00:13:870' resource-list pagelock fileid=8 pageid=7726 dbid=17 objectname=EAS.dbo.Audit_TransactionDetail id=lock4f79500 mode=U associatedObjectId=886415243542528 owner-list owner id=process3c77d68 mode=U waiter-list waiter id=process3c12c58 mode=IX requestType=wait pagelock fileid=6 pageid=334008 dbid=17 objectname=EAS.dbo.Audit_TransactionDetail id=lock846afca00 mode=IX associatedObjectId=604940266831872 owner-list owner id=process3c12c58 mode=IX waiter-list waiter id=process3c77d68 mode=X requestType=wait
3 Réponses :
Je suppose que la table T2 doit être assez grande. Est la colonne T2.Date indexée? Sinon, alors une balayage de table sur une grande table pourrait causer vos problèmes. Indexation Cette colonne pourrait optimiser la suppression en évitant la numérisation de la table. Alternativement, si les index sur col1 ou col2 ne sont pas vraiment utilisés (ou suffisamment utilisés), les déposer pourraient également éviter le problème.
À quelle fréquence ces blocages se produisent-ils? S'ils sont très peu fréquents, une solution de contournement kludgy pourrait suffire: enveloppez chaque instruction dans un bloc d'essai / attraper, dans le chèque de capture si l'erreur était due à une impasse, et si vous réessayez la commande. Vous pouvez également soigneusement utiliser OH et déposez ces Set Deadlock_Priority Pour choisir la requête Win / perdra toujours (mais vous devez équilibrer cela avec tous les appels vers la table).
avec (nolock) s. Nolock est ignoré par des inserts, des mises à jour et des suppressions.
Au lieu de poster une description de votre compréhension du graphique de blocage, afficher le graphique de blocage lui-même. le XML, et non pas une image bitmap du rendu graphique. À première vue, le fait qu'il ya un conflit sur un verrou IX impliqué suggère l'escalade de verrouillage est en cours, ce qui indique qu'il n'y a pas d'index au service du SUPPRIMER, ou que le point de basculement de l'indice sur la jointure est frappé. Mais là encore, cela est juste la spéculation en raison du manque d'information. Pour donner une réponse significative, on aurait besoin de la définition XML de blocage réel et le schéma exact de l'objet concerné.
Après UPDATE
Vous avez toujours pas fourni les renseignements demandés: le schéma exact de tous les objets impliqués, y compris les clusters et les définitions d'index non ordonnés en clusters. Jusque-là, le soupçon initial tient: le SUPPRIMER fait un scan de table et a augmenté de granularité de verrouillage de page. Cela est dû à une mauvaise indexation.
Votre évaluation que « les déclarations de suppression et insertion touche systématiquement les 2 ensembles différents de données » est erroné sur deux comptes:
Comme une note de côté, des tables de vérification sont presque toujours nécessaire d'être regroupés par date de l'incident / temps parce que toutes les requêtes sur les demandes des intervalles de temps spécifiques ( « ce qui est arrivé entre ... et ... ») et l'article cherche peut être satisfaite par une clé primaire non-cluster ID. Purge des enregistrements d'audit, même lorsqu'ils sont correctement regroupés, est criblé de problèmes de performance et nécessite Batching pour éviter une explosion de journal. La meilleure solution consiste à déployer une fenêtre coulissante automatique en utilisant le partitionnement , mais qui vient avec ses propres défis.
La règle générale pour éviter les blocages "Insérer vs Suppr" est:
Votre opération de suppression a un join compliqué et une condition "inférieure à" qui entraîne probablement une analyse de table si cette colonne n'est pas correctement indexée.
Combien de lignes sont affectées par le Supprimer?
Pouvez-vous publier la sortie T1222 d'origine?
J'ai fusionné vos comptes non enregistrés. Veuillez simplement modifier votre question pour fournir des informations supplémentaires. Les réponses doivent être des solutions à une question, pas d'informations supplémentaires :)