Discussion :

[fmichael]

Bonjour,

Je suis loin d'être un spécialiste SQL et il y a un scénario de lock que je n'arrive pas à comprendre dans SQL Server 2008, donc je me tourne vers vous

Je crée d'abord une table bidon, sans index, ni primary key (important)

Code :

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

1
2
3
4
5
6
7
 
CREATE TABLE MaTable (col1 int)
INSERT INTO MaTable VALUES(1)
INSERT INTO MaTable VALUES(2)
INSERT INTO MaTable VALUES(3)
INSERT INTO MaTable VALUES(4)
INSERT INTO MaTable VALUES(5)

J'utilise SQL Server management studio et je lance une première Query avec une Transaction (sans commit) qui met à jour l'enregistrement de valeur 2:

Code :

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

1
2
3
BEGIN TRANSACTION
UPDATE MaTable SET Col1 = Col1 WHERE Col1 = 2 

Je lance alors une deuxième query

Code :

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

1
2
3
BEGIN TRANSACTION
UPDATE MaTable SET Col1 = Col1 WHERE Col1 = 3 

La deuxième query se mettra alors en attente tant que je ne commit pas la première

Après m'avoir arraché les cheveux, je suppose que la query 1, vu qu'il n y a pas d'index va scanner tout le contenu de la table, mais pourquoi est-ce que la table n'est pas libérée une fois le scan terminé ?

Y a t-il un autre moyen d'éviter ça sans utiliser d'index ?

Merci d'avance

[Philippe Robert]

Si tu ne mets pas de COMMIT, les verrous ne sont pas libérés.


Exemple :

cette transaction va durer 1 minutes

Code :

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

1
2
3
4
5
6
7
8
 
BEGIN TRANSACTION tr1
	UPDATE	MaTable
	SET	Description = 'Test'
	WHERE	ID = 1
	WAITFOR DELAY '00:01:00'
	PRINT 'fin transaction'
COMMIT TRANSACTION tr1

Le SELECT suivant exécuté juste après va être bloquée et ne pourra être exécutée qu'à la fin de la transaction précédente, qui durera 1 minute.

Code :

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

1
2
3
4
 
SELECT	*
FROM	MaTable
WHERE	ID = 1

Et je ne vois pas pourquoi tu ne veux mettre ni clé primaire, ni index sur tes tables.

[fmichael]

Merci pour la réponse, mais la question que je me pose c'est pourquoi il verrouille tous les enregistrements de la table lors de la première query ?

Dans ton exemple, il travaille sur le même enregistrement, il est donc logique que ce soit verrouilé, mais pas toute la table quand meme ?


(bien sur, mettre une clé primaire est la moindre des choses, mais j'aimerais savoir pourquoi il ne fait pas un verrouillage par enregistrement uniquement dans ce cas)

[Sergejack]

Sql Server a effectivement besoin d'indexes pour faire un lock plus granulaire.
Et c'est logique : non seulement il faut que Sql Server puisse facillement connaître les lignes lockées mais aussi puisse facillement accéder aux autres lignes ("contourner le lock").

Je te conseil par ailleurs de te renseigner sur le LOCK ESCALATION si tu es parti dans une appli gourmande.

[SQLpro]

Pas nature les bases de données sont ensemblistes. Elles ont été conçue conformément à l'algèbre de Codd qui part de la théorie des ensembles. Or dans les ensembles mathématique il n'y a pas de notion d'ordre.

Lisez l'article que j'ai écrit à ce sujet :
http://blog.developpez.com/sqlpro/p5...sont-des-ense/

Dès lors en l'absence de clef primaire ou unique qui permet de repérer une ligne susceptible de "bouger" à tout moment, il faut verrouiller la table.

Vous devez donc comprendre que toute table devrait au moins avoir une clef !

Contrairement à une idée stupidement répandue, les mises à jour sans clef (INSERT, UPDATE, DELETE) sont pas nature plus lente et surtout induisent de la contention !
A lire :
http://blog.developpez.com/sqlpro/p7...d-index-ni-de/

Dans l'algèbre de Codd il n'est pas possible d'avoir une relation sans clef... Hélas dans les SGBDR il en va tout autrement...
Sans clef ce n'est pas une table, mais un fichier à la Cobol que vous manipulez... et encore !

A +

[fmichael]

En effet, vu comme ça, ça semble logique ... Merci beaucoup pour l'explication et les URL