Discussion :

[lacombefr]

Bonjour,


Avec les collègues nous avons constaté quelque chose d'étonnant.

Dans une transaction en mode Read Commited (par défaut sous SQL Server), nous avons fait ce test :

Dans un processus (une "nouvelle requête") sous Sql Server Management, nous avons mis un verrou sur une ligne comme ceci :

Code :

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

1
2
3
4
5
SET TRANSACTION ISOLATION LEVEL READ COMMITED
BEGIN TRANSACTION
update t_exemple where id=1234;
WAITFOR DELAY '00:00:20'
COMMIT TRANSACTION

La ligne de t_exemple dont l'id est 1234 est verrouillé pendant 20 secondes.

Dans ce laps de temps, nous avons verifié que la ligne était bien verrouillé, avec une nouvelle requête dans Sql Server Management (2eme processus sur Sql Server) avec cette procédure :

Code :

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

select * from t_exemple where id=1234;

Effectivement, la ligne était bien verrouillé car elle ne nous a été rendu qu'après la fin de la première requête.

Ensuite on a fait la même chose mais nous avons remplacé la deuxième requête par :

Code :

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

select * from t_exemple

Sans préciser que nous voulions l'id 1234.

Eh bien, le résultat comprenant l'id 1234 nous a été retourné avant la fin de la première requête.. Il semble donc qu'il n'y avait pas de verrou sur la ligne dont l'id est 1234.

Auriez vous une explication à cela ?


D'avance merci beaucoup

[diva-bibi]

Est que vous avez repeté plusieurs fois l'expérience ?

[lacombefr]

bonjour,

oui bien sur. Je vous invite à faire la même expérience vous même pour le vérifier (ou non) ..

[lacombefr]

rebonjour,

Je n'ai toujours pas résolu ce problème..

Je suis à l'affut de toutes explications..

[SQLpro]

Vous êtes totalement à côté de la plaque... en effet :

La ligne de t_exemple dont l'id est 1234 est verrouillé pendant 20 secondes.

Non ! En mode READ COMMITTED vous avez l'assurance que le SELECT lira des données validées. Une fois le SELECT effectué il peut se passer tout ce que l'on veut. Autrement dit une fois les données sont lues les verrous sont immédiatement libérés.
Ce comportement est donc parfaitement logique et attendu...

Si vous aviez lu mon livre sur SQL vous auriez vu quels sont les différents niveaux d'isolation de la norme SQL et ce que cela implique.
Néanmoins, rien ne vous empêche de lire l'article que j'ai écrit au sujet des différents niveaux d'isolation normatifs et SQL Server, avec ses différentes démonstrations : http://www.sqlspot.com/Gerer-l-isola...nsactions.html

Si vous utilisez le niveau REPEATABLE READ alors les verrous sont maintenus, mais cela n'empêche pas les anomalies transactionnelles venant de tuples fantômes...

A +

[lacombefr]

Bonjour SqlPro,


Si vous saviez le nombre de fois ou vous m'avez aidé (sous différents pseudo)..

Je vais commander votre livre dans la semaine..

Merci

[lacombefr]

Par contre je suis pas sur que vous ayiez compris mon problème (ou c'est moi qui arrive pas à vous suivre)..

En fait la premiere transaction n'est pas un SELECT mais un UPDATE.

un UPDATE fait bien un verrouillage en mode Read commited ?
Donc ma ligne est verouillé 20 secondes non ?

Par contre, c'est après, avec mes 2 SELECT (durant les 20 secondes) que je n'obtiens pas les mêmes résultats.

non ?

[rudib]

Bonjour,

C'est dû à la granularité de verrouillage ou à la ressource. Ton UPDATE verrouille une ligne, ton SELECT escalade sur la table, ou c'est un index qui est verrouillé, et une autres index pour le SELECT.

Pour en avoir le coeur net : exécute le SELECT dans une transaction en isolation REPEATABLE READ sans la refermer, et envoie-nous le résultat ensuite de ceci :

Code :

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

1
2
SELECT resource_type, request_mode, request_status, FROM sys.dm_tran_locks
WHERE request_session_id IN (spid1, spid2)

où tu remplaces spid1 et spid2 par les numéros de @@SPID de tes deux sessions. Ensuite, on détaillera

(ou la requête listée dans ce blog : http://decipherinfosys.wordpress.com...l-server-2005/)

[lacombefr]

Bonjour rudib,

Merci.

Voila j'ai fait le test.

SPID=54

Code :

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

1
2
3
4
5
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
UPDATE t_exemple SET libelle='tata' WHERE id=2;
WAITFOR DELAY '00:00:20'
COMMIT TRANSACTION

SPID=52

Code :

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

SELECT * FROM t_exemple;

J'ai exécuté la première, puis la deuxième puis :

Code :

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

1
2
SELECT resource_type, request_mode, request_status, request_session_id FROM sys.dm_tran_locks
WHERE request_session_id IN (52, 54)

Et j'ai obtenu le résultat :
DATABASE S GRANT 54
DATABASE S GRANT 52
RID X GRANT 54
PAGE IX GRANT 54
OBJECT IX GRANT 54


Par contre j'ai remarqué un autre truc :

Si la première requête (UPDATE) modifie vraiment le libellé, par exemple si j'update libelle="toto" alors que le libellé était à "tata", alors la deuxième requête (SELECT) attend que la première (transaction) ait terminée pour s'exécuter.

Mais si j'update le libelle="toto" alors qu'il était déjà à "toto", alors la requête SELECT n'attend pas la fin de la première. (c'est ça que j'avais remarqué et qui était l'objet de mon post)

C'est comme s'il y avait un mécanisme dans SQL Server qui ne verouille pas une ligne si l'UPDATE ne modifie rien "dans les faits". Je sais pas si je m'exprime bien. Et dans ce cas, c'est rassurant, et le problème ne se pose plus pour moi, car c'est ça que je craignais (qu'eune donnée soit pas vérouillée).

Merci

[rudib]

Tu n'as pas le verrou du SELECT, car tu n'étais pas en REAPEATABLE READ. Mais en effet, tu az raison : s'il n'y a pas de modification effective de la ligne, il n'y a pas de verrou posé sur la ligne. Tu peux voir la différence dans le journal de transaction entre les deux UPDATE à l'aide de la fonction non documentée fn_dblog(), qui montre ce que SQL server écrit dans le log de transactions. En cas de non-modification, il n'y a pas de réelle modification de la ligne.

[lacombefr]

rebonjour rudib,

Merci !

Ok tout s'explique. En fait mon étonnement lors de ce test était du à ce phénomène. Le verrou n'était pas posé car la ligne n'était pas modifiée "en vrai".. Tout est clair maintenant merci !

J'ai appris quelque chose aujourd'hui

Je mets en résolu

encore merci
bon après midi

[SQLpro]

En fait il faut comprnedre que :
1) READ UNCOMMITTED : pas de verrous si lecture, verrou exclusif si mise à jour, libéré en fin d'exécution de l'ordre SQL
2) READ COMMITTED : verrous en lecture comme en écriture mais exclusif dans ce dernier cas, libéré en fin d'exécution de l'ordre SQL
3) REPEATABLE READ : verrous en lecture comme en écriture mais exclusif dans ce dernier cas, libéré en fin de transaction.
4) SERIALIZABLE : verrous de table en lecture comme en écriture mais exclusif dans ce dernier cas, libéré en fin de transaction.

Pour la granularité du verrou, c'est SQL Server qui décide à l'aide de son gestionnaire de verrous.

A +