Éviter l'impasse lors de la mise à jour de la table

Question

J'ai une application à trois niveaux et j'ai des données mises en cache côté client, donc j'ai besoin de savoir quand les données ont changé sur le serveur pour synchroniser cette cache.

J'ai donc ajouté un champ "lastmodification" dans les tables, et je mets à jour ce champ lorsqu'une donnée change. Mais certaines lignes 'parent' de lastmodification doivent être mises à jour si des lignes enfants (utilisant FK) sont modifiées.
Récupérer le MAX(lastmodification) de la table principale, et MAX d'une table associée, puis le MAX de ces plusieurs valeurs fonctionnait mais était un peu lent. Je veux dire:

MAX(MAX(MAIN_TABLE), MAX(CHILD1_TABLE), MAX(CHILD2_TABLE))

J'ai donc changé et ajouté un déclencheur sur cette table afin qu'il mette à jour un champ dans une table TBL_METADATA:

CREATE TABLE [TABLE_METADATA](
    [TABLE_NAME] [nvarchar](250) NOT NULL,
    [TABLE_LAST_MODIFICATION] [datetime] NOT NULL

Maintenant, la table associée peut mettre à jour l'heure de dernière modification de la table 'principale' en mettant également à jour la dernière modification dans la table de métadonnées. Récupérer le lastmodification est maintenant rapide

Mais... maintenant j'ai des deadlocks aléatoires liés à la mise à jour de cette table.

Cela est dû à 2 transactions modifiant la TABLE_METADATA à une étape différente, et se bloquant mutuellement.

Ma question : Voyez-vous un moyen de garder cette mise à jour de lastmodification sans bloquer la ligne ? Dans mon cas, je m'en fiche vraiment si :

• Le lastmodification reste mis à jour même si la transaction est annulée
• Le lastmodification 'sale' (mis à jour mais pas encore validé) est écrasé par une nouvelle valeur

En fait, je n'ai vraiment pas besoin que ces mises à jour soient dans la transaction, mais comme elles sont exécutées par le déclencheur, elles sont automatiquement dans la transaction actuelle.

Merci pour toute aide

Answer 1

À ma connaissance, vous ne pouvez pas empêcher un verrou U. Cependant, vous pourriez essayer de réduire le nombre de verrous au minimum en utilisant with (rowlock). Cela indiquera à l'optimiseur de requête de verrouiller les lignes une par une au fur et à mesure de leur mise à jour, plutôt que d'utiliser un verrouillage de page ou de table.

Vous pouvez également utiliser with (nolock) sur des tables jointes à la table en cours de mise à jour. Une alternative serait d'utiliser set transaction isolation level read uncommitted. Faites attention en utilisant cette méthode, car vous pourriez potentiellement créer des données corrompues.

Par exemple:

update mt with (rowlock)
    set SomeColumn = Something
    from MyTable mt
        inner join AnotherTable at with (nolock)
        on mt.mtId = at.atId

Vous pouvez également ajouter with (rowlock) et with (nolock)/set transaction isolation level read uncommitted à d'autres objets de base de données qui lisent et écrivent souvent la même table, pour réduire encore plus la probabilité d'un deadlock.

Si des deadlocks se produisent toujours, vous pouvez réduire le verrouillage en lecture sur la table cible en faisant une auto-jointure comme ceci:

update mt with (rowlock)
    set SomeColumn = Something
    from MyTable mt
    where mt.Id in (select Id from MyTable mt2 where Column = Condition)

Vous pouvez trouver plus de documentation sur les astuces de table ici.