Discussion :

[chris92]

Bonjour,
J'ai rencontré récemment un problème de LOCK qui me laisse perplexe:

J'ai un processus d'intégration qui utilise une table de travail:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
TABLE TRAVAIL (NUM_LINE numeric(18, 0) IDENTITY(1,1) NOT NULL
,COD_UTI VARCHAR(10)
,CHAMP1 VARCHAR(25)
,CHAMP2 VARCHAR(25)
...
)

Je précise qu'il n'y a pas d'index sur cette table étant donné que c'est une table 'tampon', donc les données y sont continuellement écrites, mises a jour, supprimées (mais peut etre ai-je tort ?).

Plusieurs processus d'intégration peuvent être lancés en même temps sur la table, chaque processus étant identifié par le champ COD_UTI.

Le processus d'intégration effectue des UPDATE sur la table TRAVAIL, et utilise également des curseurs pour y effectuer des opérations.
Tous les curseurs sont en LOCAL FAST_FORWARD / NOLOCK (sur le select).

Or malgré cela, j'ai des accés concurrents a ma table de travail, bien que chaque processus travaille avec un COD_UTI bien spécifique.
(apperemmebt, SQL SERVER lock toute la table au dela d'un certin nombre d'enregistrement à selectionner ou updater)

J'ai essayé de passer tous les UPDATE en option ROWLOCK, mais ca ne semble pas suffire.

Est ce que je dois créer un index sur la colonne cod_uti pour que la table soit lockée UNIQUEMENT sur le cod_uti d'un process donné ?

[chris92]

Après avoir rajouté des PK sur mes SEQ_LIG, ca va 1000 fois plus vite.
Les lock venaient certainement de la: temps de scan de toute la table trop long.

[mikedavem]

Bonsoir,

Je précise qu'il n'y a pas d'index sur cette table étant donné que c'est une table 'tampon', donc les données y sont continuellement écrites, mises a jour, supprimées (mais peut etre ai-je tort ?).

Oui et visiblement vous l'avez remarqué.

J'ai essayé de passer tous les UPDATE en option ROWLOCK, mais ca ne semble pas suffire.

Si SQL Server estime qu'il faut utiliser un TABLOCK plutôt qu'un ROWLOCK il le fera et même si vous lui indiqué un ROWLOCK.

De plus si vous n'aviez aucun index, le scan était inévitable et le TABLOCK aussi.

++

[SQLpro]

Une table sans clef n'a pas d'ordre des lignes. Une table est ensembliste par nature et dans un ensemble de données, la notion d'ordre des lignes n'existe pas. Lisez ceci :

Dès lors, rechercher la moindre ligne revient à balayer toute la table. Pour le curseur c'est différents car il commence à verrouiller la première ligne, puis la seconde... jusqu'à ce qu'il se rende compte qu'il a trop de verrous et passe donc à un verrous de page, puis d'extension, puis de table...

Bref, contrairement aux idées reçues, même la mise à jour est plus rapide si l'on pose les bons index (et les bonnes clef) car le repérage des lignes sera immédiat et la granularité des verrous faible.

Vous l'avez appris à vos dépends....

Leçon que l'on peut résumer à :
"dans une base de données relationnelle, il convient de faire des relations"

Or les relations au sens mathématique du terme doivent toujours posséder une clef. Dans le cas contraire, vous faites équivalent de traitements de fichier COBOL !

A +

[chris92]

Oui, je pense que j'ai bien retenu la leçon : vive les index

Pour information j'ai également différents lots SSIS qui attaquent la table TRAVAIL pour y charger des données, et l'option par défaut du bulkload SSIS est "OpenRowSet with fast load". Les lock concurrents venaient de là:
Cette option "fastLoad" fait un lock de TOUTE la table afin d'y insérer les données en masse plus vite.

Il faut utiliser l'option "OpenRowSet" simple qui ne locke pas toute la table et permet donc a différents process utilisant des lots ssis de travailler sereinement sur la même table.

A+

[SQLpro]

D'où l'intérêt d'utiliser directement la commande bcp ou BULK INSERT, au moins on sait ce qu'on fait plutôt que de passer par une couche graphique comme SSIS qui masque tout !

A +

[chris92]

Oui pour un chargement simple, mais certains des lots SSIS font pas mal de traitements assez lourds qu'il serait fastidieux de traiter avec un bulk insert + un autre process.
La je gère cela directement dans SSIS avec l'ajout de scripts .NET

A+