Le mode Auto-Commit

Sous SQL Server, le mode auto-commit est activé par défaut. Donc à chaque exécution d'une requête de modification, par défaut, le client va valider la transaction. Sur Oracle, la transaction est validée uniquement lorsque le client passe l'instruction "COMMIT".
Sur mon benchmark, cela donne le schéma suivant : Mode auto-commit SQL Server
On voit bien que sur le moteur SQL Server, la transaction qui réalise les UPDATES va être commitée 3 fois alors que sur Oracle, il n'y a qu'un seul commit lorsque les 3 UPDATES sont réalisés.
Ce mode "auto-commit" peut être désactivé et comme je cherche à comparer les 2SGBDR sur un fonctionnement identique, je l'ai désactivé.

Désactivation du mode auto-commit

Pour désactiver en Java le mode auto-commit de SQL Serveur, c'est très simple :

Disable AutoCommit

A partir ce là, les transactions sous SQL Server sont validées lorsque vous donnez l’instruction de COMMIT.
Instruction Java de commit

Par contre, on commence à avoir des erreurs ! Des erreurs de Dead Locks (ça fait peur) !
Par défaut (et je n'ai rien modifié pour l'instant), SQL Serveur est sur un niveau d'isolation où les SELECT posent des locks (Read Commited) ; voici un schéma pour vous présenter ce qui se passe en interne :
Schéma de deadlock sur Update-Select

Si on désactive le mode Auto-Commit, les locks ne sont libérés que lors du COMMIT et puisque nous sommes en mode Read Commited, les SELECTS posent des locks (Shared) sur les lectures. C'est assez contre intuitif, mais 2 transactions peuvent ne pas entrer en collision si elles mettent à jour des lignes différentes dans les tables par contre, si elles lisent des données qui sont en cours de modification par une autre transaction, vous pouvez rencontrer des dead locks.

Changement de mode d'isolation

On continue notre paramétrage de SQL Server. Pour ne plus poser de lock lors de SELECT, je passe en mode Snapshot Isolation (cf mon précédent article pour l'explication du Snapshot Isolation).
ALTER DATABASE dbaInstanceManager SET ALLOW_SNAPSHOT_ISOLATION ON
Ce changement ne suffit pas à m'éviter les dead locks. Chose étonnante, les dead locks apparaissent lors d'updates qui ne touchent pas aux mêmes lignes mais qui posent des locks de type U sur une même ressource de type RID.

Par contre, comme je suis dans l'optique de comparer Oracle avec SQL Server, j'ai modifié côté client le niveau d'isolation pour SQL Server afin qu'il ait le même comportement qu'Oracle. Voici le code que j'ai appliqué :
Positionne le niveau d'isolation
Le thread témoin sera en mode d'isolation ReadCommited donc à chacun de ses lectures, il prendra les dernières valeurs commitées.
Les autres threads seront en mode d'isolation Snapshot. Il lieront toujours les valeurs qui étaient commitées lors du démarrage de leur transaction.
C'est exactement le mode de fonctionnement pour Oracle et avec ce paramétrage côté client, plus aucun dead lock.

Comparaison des attentes

Pour ce test, je n'ai rien changé sur le test pour Oracle ; par contre, sur SQL Server, j'ai supprimé le mode AutoCommit et j'ai activé l'Isolation Snapshot ainsi que configuré les threads pour utiliser ce mode-là.
Voici les résultats des événements d'attente :
Attentes en Snapshot Isolation

Analyse

Sur le graphique, j'ai diminué le SELECT ratio comme précédemment sur mes autres benchmarks.
Cette fois, je vous mets sur le même graph 2 séries d'attentes :

  • Série Default = attentes lorsque vous avez le mode d'isolation par défaut et le benchmark en mode AutoCommit
  • Série RCSI/RC = attentes lorsque vous êtes en Snapshot Isolation et le benchmark en Commit explicites

Vous pouvez voir qu'il n'y a plus aucune attente de type LCK_M_S dès qu'on est en snapshot isolation. En effet, le thread de monitoring ne réalise que des SELECTS et en mode snapshot isolation, les SELECTS ne posent pas de locks.
Par contre, avec ce mode, les attentes en PAGELATCH_SH sont importantes et augmentent très rapidement (exponentiellement ?) quand le SELECT ratio diminue.

Je vous épargne la comparaison avec Oracle : d'après mes tests, Oracle est moins sensible (voir pas du tout) à une chute du select_ratio. Je vous conseille de bien garder à l'esprit les effets des DML sur les SELECT sous SQL Server.
Experts SQL Server, j'attends vos remarques sur mon analyse :)