les requêtes de mise à jour et d'insertion créent un blocage

Question

Je vais essayer d'expliquer mon problème de la manière la plus détaillée possible, et j'apprécierais toute aide/suggestion. Mon problème concerne un blocage causé par deux requêtes (une insertion et une mise à jour). J'utilise MS-SQL server 2008

J'ai deux applications qui utilisent la même base de données :

1. Application web (à chaque requête, plusieurs enregistrements sont insérés dans la table Impressions en appelant une procédure stockée)
2. Service Windows (calcule toutes les impressions réalisées en une minute, chaque minute, pour la minute précédente et met un drapeau sur chacune des impressions calculées via une procédure stockée également).

L'application web insère les enregistrements d'impressions sans utiliser de transaction, tandis que l'application de service Windows calcule les impressions en utilisant une transaction. IsolationLevel.ReadUncommitted transaction. La procédure stockée dans l'application de service Windows fait quelque chose comme ceci :

Procédure stockée du service Windows :

Passe en revue toutes les impressions qui ont l'attribut isCalculated défini à false et date < @now , incrémente un compteur et d'autres données dans une autre table connectée à la table d'impressions, et définit l'attribut isCalculated à true sur les impressions qui ont une date < @now. Parce que cette procédure stockée est assez grosse, pas la peine de la coller, voici un extrait de code raccourci de ce que fait la proc :

DECLARE @nowTime datetime = convert(datetime, @now, 21) 
DECLARE dailyCursor CURSOR FOR

SELECT  Daily.dailyId, 
        Daily.spentDaily, 
        Daily.impressionsCountCache ,
        SUM(Impressions.amountCharged) as sumCharged, 
        COUNT(Impressions.impressionId) as countImpressions
FROM    Daily INNER JOIN Impressions on Impressions.dailyId = Daily.dailyId
WHERE   Impressions.isCharged=0 AND Impressions.showTime < @nowTime AND Daily.isActive = 1
GROUP BY Daily.dailyId, Daily.spentDaily, Daily.impressionsCountCache

OPEN dailyCursor

DECLARE @dailyId int, 
        @spentDaily decimal(18,6), 
        @impressionsCountCache int, 
        @sumCharged decimal(18,6), 
        @countImpressions int

FETCH NEXT FROM dailyCursor INTO @dailyId,@spentDaily, @impressionsCountCache, @sumCharged, @countImpressions

WHILE @@FETCH_STATUS = 0
    BEGIN   

        UPDATE Daily 
        SET spentDaily= @spentDaily + @sumCharged, 
            impressionsCountCache = @impressionsCountCache + @countImpressions
        WHERE dailyId = @dailyId

        FETCH NEXT FROM dailyCursor INTO @dailyId,@spentDaily, @impressionsCountCache, @sumCharged, @countImpressions
    END
CLOSE dailyCursor
DEALLOCATE dailyCursor

UPDATE Impressions 
SET isCharged=1 
WHERE showTime < @nowTime AND isCharged=0

Procédure stockée de l'application Web :

Cette procédure est assez simple, elle insère simplement l'enregistrement dans la table. Voici un extrait de code raccourci :

INSERT INTO Impressions 
(dailyId, date, pageUrl,isCalculated) VALUES 
(@dailyId, @date, @pageUrl, 0)

Le code

Le code qui appelle ces procédures stockées est assez simple : il crée simplement les commandes SQL en passant les paramètres nécessaires et les exécute.

//i send the date like this
string date = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff", 
CultureInfo.InvariantCulture);

SqlCommand comm = sql.StoredProcedureCommand("storedProcName", 
parameters, values);

Je rencontre très souvent des blocages (les exceptions se produisent dans l'application web, pas dans le service Windows), et après avoir utilisé le SQL-Profiler, j'ai découvert que les blocages se produisent probablement à cause de ces deux requêtes (je n'ai pas beaucoup d'expérience dans l'analyse des données des profileurs).

Les dernières données de trace recueillies à partir du profileur du serveur SQL se trouvent au bas de cette question.

En théorie, ces deux procédures stockées devraient pouvoir fonctionner ensemble car la première insère les enregistrements un par un avec date=DateTime.Now, et la seconde calcule les Impressions qui ont une date < DateTime.Now.

Edit :

Voici le code exécuté dans l'application de service Windows :

SQL sql = new SQL();
DateTime endTime = DateTime.Now;
//our custom DAL class that opens a connection
sql.StartTransaction(IsolationLevel.ReadUncommitted);
try
{
    List<string> properties = new List<string>() { "now" };
    List<string> values = new List<string>() { endTime.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture) };
    SqlCommand comm = sql.StoredProcedureCommannd("ChargeImpressions", properties, values);
    comm.Transaction = sql.Transaction;
    ok = sql.CheckExecute(comm);
}
catch (Exception up)
{
    ok = false;
    throw up;
}
finally
{
    if (ok)
      sql.CommitTransaction();
    else
      sql.RollbackTransactions();
    CloseConn();
}

EDIT :

J'ai ajouté les index sur les deux tables comme suggéré par Martin Smith comme ceci :

CREATE NONCLUSTERED INDEX [IDX_Daily_DailyId] ON [dbo].[Daily] 
(
    [daily] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

y

CREATE NONCLUSTERED INDEX [IDX_Impressions_isCharged_showTime] ON [dbo].[Impressions] 
(
    [isCharged] ASC,
    [showTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Pour l'instant, pas d'exception, je ferai un rapport plus tard

Edit :

Malheureusement, cela n'a pas permis de résoudre le problème de blocage. Je vais lancer une trace de deadlock dans le profiler pour voir si les deadlocks sont les mêmes qu'avant.

Edit :

J'ai collé la nouvelle trace (pour moi, elle ressemble à la précédente), je n'ai pas pu capturer un écran du plan d'exécution (il est trop grand) mais voici le xml du plan d'exécution Et voici une capture d'écran du plan d'exécution de la requête d'insertion :

 <deadlock victim="process14e29e748">
  <process-list>
   <process id="process14e29e748" taskpriority="0" logused="952" waitresource="KEY: 6:72057594045071360 (f473d6a70892)" waittime="4549" ownerId="2507482845" transactionname="INSERT" lasttranstarted="2011-09-05T11:59:16.587" XDES="0x15bef83b0" lockMode="S" schedulerid="1" kpid="2116" status="suspended" spid="65" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-09-05T11:59:16.587" lastbatchcompleted="2011-09-05T11:59:16.587" clientapp=".Net SqlClient Data Provider"  hostpid="2200"  isolationlevel="snapshot (5)" xactid="2507482845" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="dbo.InsertImpression" line="27" stmtstart="2002" stmtend="2560" sqlhandle="0x03000600550e30512609e200529f00000100000000000000">
INSERT INTO Impressions 
    (dailyId, languageId, showTime, pageUrl, amountCharged, age, ipAddress, userAgent, portalId, isCharged,isCalculated) VALUES 
    (@dailyId, @languageId, @showTime, @pageUrl, @amountCharged, @age, @ip, @userAgent, @portalId, 0, 0)     </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 1362103893]    </inputbuf>
   </process>
   <process id="process6c9dc8" taskpriority="0" logused="335684" waitresource="KEY: 6:72057594045464576 (5fcc21780b69)" waittime="4475" ownerId="2507482712" transactionname="transaction_name" lasttranstarted="2011-09-05T11:59:15.737" XDES="0x1772119b0" lockMode="U" schedulerid="2" kpid="3364" status="suspended" spid="88" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2011-09-05T11:59:15.737" lastbatchcompleted="2011-09-05T11:59:15.737" clientapp=".Net SqlClient Data Provider"  hostpid="1436" isolationlevel="read uncommitted (1)" xactid="2507482712" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
    <executionStack>
     <frame procname="dbo.ChargeImpressions" line="60" stmtstart="4906" stmtend="5178" sqlhandle="0x03000600e3c5474f0609e200529f00000100000000000000">
UPDATE Impressions 
    SET isCharged=1 
    WHERE showTime &amp;lt; @nowTime AND isCharged=0

    </frame>
    </executionStack>
    <inputbuf>
Proc [Database Id = 6 Object Id = 1330103779]    </inputbuf>
   </process>
  </process-list>
  <resource-list>
   <keylock hobtid="72057594045071360" dbid="6" objectname="dbo.Daily" indexname="PK_Daily" id="lock14c6aab00" mode="X" associatedObjectId="72057594045071360">
    <owner-list>
     <owner id="process6c9dc8" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process14e29e748" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594045464576" dbid="6" objectname="dbo.Impressions" indexname="IDX_Impressions_isCharged_showTime" id="lock14c901200" mode="X" associatedObjectId="72057594045464576">
    <owner-list>
     <owner id="process14e29e748" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="process6c9dc8" mode="U" requestType="wait"/>
    </waiter-list>
   </keylock>
  </resource-list>
 </deadlock>

Edit :

Après des suggestions de Jonathan Dickinson :

1. J'ai modifié la procédure stockée (j'ai supprimé le curseur),
2. J'ai modifié l'IDX_Impressions_isCharged_showTime pour ne pas autoriser les PAGE_LOCKS et les
3. J'ai ajouté -1 seconde à la propriété @now dans l'application du service Windows pour éviter les cas limites de blocage.

Mise à jour :

Le temps d'exécution des requêtes a diminué après les dernières modifications, mais pas le nombre d'exceptions.

J'espère que c'est la dernière mise à jour :

Les changements proposés par Martin Smith sont maintenant en ligne, la requête d'insertion utilise maintenant l'index non groupé et en théorie cela devrait résoudre le problème. Pour l'instant, aucune exception n'a été signalée (je croise les doigts).

Answer 1

Évitez les curseurs, cette requête n'en avait pas besoin. Le SQL est no un langage impératif ( c'est pourquoi il a une mauvaise réputation parce que tout le monde l'utilise comme tel. ) - c'est un langage figé.

La première chose que vous pouvez faire est d'accélérer l'exécution de base de votre SQL, moins de temps pour analyser/exécuter la requête signifie moins de chance de blocage :

• Préfixez toutes vos tables par [dbo] - Cela permet de réduire jusqu'à 30 % l'étape de l'analyse syntaxique.
• Alias vos tables - cela réduit un peu l'étape de planification.
• Citation d'identifiants mai accélérer les choses.
• Ce sont les conseils d'un ex-SQL-PM avant que quelqu'un ne décide de le contester.

Vous pouvez utiliser un CTE pour obtenir les données à mettre à jour, puis utiliser un fichier de type UPDATE ... FROM ... SELECT pour effectuer les mises à jour réelles. Cela sera plus rapide qu'un curseur, car les curseurs sont chien lent par rapport à des opérations en plateau propre (même le plus rapide des curseurs à tuyau d'incendie comme le vôtre). Moins de temps passé à mettre à jour signifie moins de risques de blocage. Note : Je n'ai pas vos tables d'origine, je ne peux pas valider ceci - vérifiez donc avec une base de données de développement.

DECLARE @nowTime datetime = convert(datetime, @now, 21);

WITH [DailyAggregates] AS
(
    SELECT  
        [D].[dailyId] AS [dailyId],
        [D].[spentDaily] AS [spentDaily],
        [D].[impressionsCountCache] AS [impressionsCountCache],
        SUM([I].[amountCharged]) as [sumCharged],
        COUNT([I].[impressionId]) as [countImpressions]
        FROM [dbo].[Daily] AS [D]
            INNER JOIN [dbo].[Impressions] AS [I]
               ON [I].[dailyId] = [D].[dailyId]
        WHERE [I].[isCharged] = 0
          AND [I].[showTime] < @nowTime 
          AND [D].[isActive] = 1
    GROUP BY [D].[dailyId], [D].[spentDaily], [D].[impressionsCountCache]
)
UPDATE [dbo].[Daily]
    SET [spentDaily] = [A].[spentDaily] + [A].[sumCharged],
        [impressionsCountCache] = [A].[impressonsCountCache] + [A].[countImpressions]
    FROM [Daily] AS [D]
    INNER JOIN [DailyAggregates] AS [A]
       ON [D].[dailyId] = [A].[dailyId];

UPDATE [dbo].[Impressions]
SET [isCharged] = 1 
WHERE [showTime] < @nowTime 
  AND [isCharged] = 0;

En outre, vous pourriez interdire les verrous de PAGE sur votre index, ce qui réduira les risques que quelques lignes verrouillent une page entière (en raison de l'escalade de verrouillage, seul un certain pourcentage de lignes doit être verrouillé avant que la page entière ne le soit).

CREATE NONCLUSTERED INDEX [IDX_Impressions_isCharged_showTime] ON [dbo].[Impressions]              
(
    [showTime] ASC, -- I have a hunch that switching these around might have an effect.
    [isCharged] ASC  
)
WITH (ALLOW_PAGE_LOCKS = OFF)
ON [PRIMARY] 
GO

Cela ne fera qu'atténuer les chances d'une impasse. Vous pouvez essayer de restreindre @now à une date dans le passé (c'est à dire today - 1 day ) pour s'assurer que la ligne insérée n'entre pas dans le prédicat de mise à jour ; il y a de fortes chances que cela empêche complètement le blocage.

Answer 2

Le curseur de votre service Windows met à jour diverses lignes dans Daily pour lequel il faut X serrures. Ils ne seront pas libérés avant la fin de la transaction.

Votre application Web effectue ensuite une insertion dans Impressions et conserve un X sur la ligne nouvellement insérée pendant qu'il attend un verrou S sur l'une des rangées dans Daily qui sont verrouillés par l'autre processus. Il doit les lire pour valider la contrainte FK.

Votre service Windows effectue alors la mise à jour sur Impressions en prenant U verrouille les rangées qu'il scanne en cours de route. Il n'y a pas d'index qui lui permette de chercher dans les rangées, donc cette analyse inclut la rangée ajoutée par l'application web.

Alors

(1) Vous pourriez ajouter un indice composite à Impressions sur showTime, isCharged ou vice-versa (vérifiez les plans d'exécution) pour permettre aux lignes que le service Windows va mettre à jour d'être trouvées par une recherche d'index plutôt que par un balayage complet.

-Ou

(2) Vous pourriez ajouter un index redondant non clusterisé sur Daily(DailyId) . Il sera beaucoup plus étroit que celui en grappes, de sorte que la validation FK l'utilisera probablement de préférence à la nécessité de disposer d'un fichier S verrou sur la ligne de l'index en grappe.

Modifier

Avertissement : Ce qui suit est basé sur des suppositions et des observations plutôt que sur des documents que j'ai trouvés !

Il semble que l'idée (2) ne fonctionne pas "telle quelle". Le plan d'exécution montre que la validation FK continue à se produire contre l'index clusterisé, même si un index plus étroit est maintenant disponible. sys.foreign_keys a des colonnes referenced_object_id, key_index_id Je suppose que la validation se fait toujours sur l'index indiqué et que l'optimiseur de requêtes n'envisage pas d'autres solutions, mais je n'ai rien trouvé qui le prouve.

J'ai constaté que les valeurs pertinentes dans sys.foreign_keys et le plan de requête a changé pour commencer à utiliser l'index plus étroit après que j'ai supprimé et ajouté à nouveau la contrainte de clé étrangère.

CREATE TABLE Daily(
    DailyId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED  NOT NULL,
    Filler CHAR(4000) NULL,
) 

INSERT INTO Daily VALUES ('');

CREATE TABLE Impressions(
    ImpressionId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    DailyId INT NOT NULL CONSTRAINT FK REFERENCES Daily (DailyId), 
    Filler CHAR(4000) NULL,
)

/*Execution Plan uses clustered index - There is no NCI*/ 
INSERT INTO Impressions VALUES (1,1) 

ALTER TABLE Daily ADD CONSTRAINT
    UQ_Daily UNIQUE NONCLUSTERED(DailyId) 

/*Execution Plan still use clustered index even after NCI created*/    
INSERT INTO Impressions VALUES (1,1) 

ALTER TABLE Impressions DROP CONSTRAINT FK
ALTER TABLE Impressions  WITH CHECK ADD  CONSTRAINT FK FOREIGN KEY(DailyId)
REFERENCES Daily (DailyId)    

/*Now Execution Plan now uses non clustered index*/    
INSERT INTO Impressions VALUES (1,1)    

Answer 3

Je suis sûr que les changements suggérés par les autres réponses sont nécessaires puisque, par exemple, l'utilisation d'un curseur n'est pas nécessaire dans votre cas... d'après le code que vous avez fourni, il n'y a même pas besoin de l'élément WHILE aussi...

Je ne suis pas un spécialiste de SQL Server... Si j'avais besoin de faire ce que votre procédure stockée fait, je m'assurerais que @nowTime = DateTime.Now.AddSeconds(-1) et le coder de la manière suivante :

BEGIN

UPDATE Daily D SET 
D.spentDaily= D.spentDaily + (SELECT SUM(I.amountCharged) FROM Impressions I WHERE I.isCharged=0 AND I.showTime < @nowTime AND I.DailyId = D.DailyId), 
D.impressionsCountCache = D.impressionsCountCache + (SELECT COUNT(I.impressionId) FROM Impressions I WHERE I.isCharged=0 AND I.showTime < @nowTime AND I.DailyId = D.DailyId)
WHERE D.DailyId IN (SELECT I.DailyId FROM Impressions I WHERE I.isCharged=0 AND I.showTime < @nowTime AND I.DailyId = D.DailyId) AND D.isActive = 1;

UPDATE Impressions I SET
I.isCharged=1 
WHERE I.showTime < @nowTime AND I.isCharged=0;

COMMIT;

END

Même avec une charge élevée, je n'ai jamais eu de problème de blocage avec aucun système parallèle. INSERT / UPDATE / DELETE sur Impressions de cette façon (bien que ce soit Oracle)... HTH