Comment fonctionne SQL Server sur la même requête avec ou sans transaction ?

Bonjour à tous

pour ma compréhension j'aurai voulu comprendre ce que faisait exactement SQL Server dans les 2 cas suivants :

1er cas

Code:

---

delete from nom_table where colonneX="toto"

---

est-ce qu'il fait une copie des lignes supprimées en mémoire ?
est-ce qu'il crée une table temporaire dans la base tempdb avec les lignes ?
si on annule la requête , que se passe t-il ?

Ce que j'espère avoir compris :
Tant qu'on annule pas la requête , les lignes concernées commencent à être supprimées et on ne pourra plus les récupérer.
Dans ce cas on supprime directement les lignes dans les fichiers physiques de la base
la base tempdb n'est pas utilisée
ou peut-être que sql écrit d'abord dans tempdb et à un moment donné (checkpoint ?) vide la base et supprime les fichier physique


2eme cas

Code:

---

1 2 3

begin trans delete from nom_table where colonneX="toto" commit trans

---

sql crée une table temporaire dans la base tempdb avec les lignes ?
si on fait un rollback , il supprime la table temporaire de tempdb ?
si on fait un commit , il supprime les lignes dans la table , supprime la table temporaire et les lignes dans le fichier data ?

derrière mes incompréhensions / mes doutes , si on exécute les 2 req je constate que la 1ère est plus rapide mais je m'aperçois que je ne sais pas l'expliquer
clairement ...c'est que je n'ai pas tout compris

je comprends bien la différence avec le rôle de la transaction pour conserver une intégrité des données..
ma question porte plus ce qui se passe concrètement derrière

merci d'avance

Bonjour,

En fait, les deux cas que vous présentez sont identiques, puisque dans le cas 1, il y a une transaction implicite : toute opération est transactionnée.
La différence de temps de traitement que vous avez observée doit probablement être due à un effet de bord ou a de mauvaises conditions de test. Comment avez-vous testé ?

Pour le reste, lors de la commande de suppression, les lignes sont effectivement supprimées en mémoire (et seront effectivement supprimées du disque par le lazy writer lors d'un checkpoint)
Les modifications ainsi effectuées sont en même temps consignées dans le journal des transactions (et non dans la tempdb).

en cas de validation, la transaction est simplement validée, fin de l'histoire.
En cas d'annulation de la transaction, le journal des transactions permet de restaurer les modifications effectuées.

Je vous confirme il n'y a strictement aucune différence ni en fonctionnement ni en temps de traitement....

Néanmoins en ce qui concerne le calcul du temps de traitement, vous devez opérer ainsi :
1) utiliser un serveur de test sur lequel AUCUN AUTRE utilisateur ne manipule les données
2) faire afficher le temps de traitement à l'aide de la commande SET STATISTICS TIME ON;
3) lancer 11 fois le traitement de suite (par exemple avec un GO 11)
4) retirer la première exécution qui met en cache les données (donc ajoute du temps du fait des lectures physiques)
5) retirer les statistiques portant le temps écoulé plus court et le plus long
6) faire une moyenne des 8 résultats
7) tenir compte d'une erreur qui peut aller jusqu'à 31 ms

Citation:

---

est-ce qu'il fait une copie des lignes supprimées en mémoire ?

---

Non

Citation:

---

est-ce qu'il crée une table temporaire dans la base tempdb avec les lignes ?

---

Non

Citation:

---

si on annule la requête , que se passe t-il ?

---

Les lignes reviennent à leur état d'origine

Citation:

---

Ce que j'espère avoir compris :
Tant qu'on annule pas la requête , les lignes concernées commencent à être supprimées et on ne pourra plus les récupérer.

---

Si vous avez lancé une transaction explicite via BEGIN TRANSACTION, alors vous pouvez choisir de finaliser par une validation (COMMIT) ou une annulation (ROLLBACK)
La finalisation rend définitive la transaction. Soit les lignes sont définitivement supprimées et vous ne pourrez pas revenir en arrière, soit elles reviennent à leur état initial.

Citation:

---

Dans ce cas on supprime directement les lignes dans les fichiers physiques de la base

---

Non, les mises à jour se font en mémoire sur les données.

Citation:

---

la base tempdb n'est pas utilisée

---

Vrai

Citation:

---

ou peut-être que sql écrit d'abord dans tempdb et à un moment donné (checkpoint ?) vide la base et supprime les fichier physique

---

Tempdb n'est utilisé que si le volume des opérations à faire nécessite, AU COURS DU TRAITEMENT, un stockage temporaire qui prendrait trop de place en RAM. Par exemple vous voulez supprimer 300 millions de lignes à l'aide d'un DELETE qui fait appel à une sous requêtes ayant une dizaine de jointures avec un prédicat complexe... Dans ce cas il est possible que la tempdb soit utilisée afin de ne pas faire exploser le besoin de cache qui conduirait à expulser du cache d'autres données...

Voici ce que je dis dans mon livre sur SQL (collection Synthex - Pearson Education) :

Pour assurer une bonne gestion des transactions SQL, le SGBDR utilise des mécanismes de verrouillage et la journalisation des opérations. Le verrouillage permet d'assurer l'exclusivité d'accès à une ressource pour un traitement particulier (par exemple une ligne d'une table dans le cas d'une mise à jour de données). La pose des verrous est donc transparente pour l'utilisateur. La journalisation permet d'enregistrer toutes les demandes de requête et les valeurs des données manipulées avant et après l'exécution de la requête afin de pouvoir revenir à un état antérieur.

Voici ce que je dis dans mon livre sur SQL Server 2014 (Eyrolles) p590 :

Journaux de transactions

Le concept du journal des transactions est né des travaux de Gray et Bernstein à la fin des années 1970.
L’idée est d’assurer la bonne finalisation d’une transaction, par exemple la reprise du système en cas de
panne. Pour ce faire, on enregistre dans un fichier, appelé journal, la transcription de la transaction
ainsi que les images « avant » des données. Les mises à jour sont effectuées en mémoire et une fois ces
opérations terminées, la transaction validée est considérée comme achevée et marquée comme telle
dans le journal. Quant aux données modifiées figurant en mémoire (pages sales ou dirty pages), elles
sont écrites de manière asynchrone, par sessions, déclenchées soit de manière régulière (par exemple,
toutes les minutes), soit avant le déclenchement planifié si le système est inactif.
Une fois les données réellement écrites, une contremarque (CHECKPOINT) est placée dans le journal afin
d’indiquer que jusque-là, toutes les transactions sont définitivement enregistrées.

A +

Merci beaucoup pour votre réponse

Quand on fait un delete/update/insert ça vient écrire dans le fichier journal les lignes à supprimer/modifier/insérer et à un moment donné les suppressions /modifications/creations vont se faire dans le fichier data.
La base tempdb n'intervient jamais dans ce genre de cas ?
Ce qui déclenche la bascule dans le fichier data c'est la fin de la transaction qu'elle soit implicite ou pas ?
merci

Citation:

---

Envoyé par jporet

Merci beaucoup pour votre réponse

Quand on fait un delete/update/insert ça vient écrire dans le fichier journal les lignes à supprimer/modifier/insérer et à un moment donné les suppressions /modifications/creations vont se faire dans le fichier data.
La base tempdb n'intervient jamais dans ce genre de cas ?
Ce qui déclenche la bascule dans le fichier data c'est la fin de la transaction qu'elle soit implicite ou pas ?
merci

---

En simplifiant rapidement, SQL Server, comme les autres SGBDR, ne travaille qu'en mémoire.
A chaque fois qu'une donnée (techniquement une page) doit être lue, modifiée, supprimée, si elle n'y est pas déjà, elle est montée en mémoire (buffer cache).

S'il s'agit d'une modification, la modification est enregistrée en mémoire (log record) et la page concernée est marquée comme "dirty".
Une page peut être modifiée plusieurs fois au cours d'une même transaction, à chaque fois un log record est créé, en mémoire.

Une fois que le SGBDR reçoit le COMMIT (implicit ou explicit), il va obligatoirement écrire tous les logs records concernés dans le Journal des transactions, sur disque.
Une fois seulement que les écritures sont confirmées, l'utilisateur reçoit le retour qui confirme la bonne exécution du COMMIT.
Ça s'appelle le Write-ahead logging.

La vie de la transaction s'arrête là. Rien n'a été écrit dans le fichier des data.

Si le SGBDR crash juste après le COMMIT, il pourra rejouer les log records présents dans le journal des transactions pour retourner à l'état consistant d'avant crash.

Ce sont les Checkpoints automatiques, qui vont régulièrement prendre toutes les "dirty pages" et les écrire dans les fichiers de données, donc de manière "asynchrone" par rapport aux transactions SQL.

C'est pourquoi on préfère utiliser les disques les plus rapides (ayant le moins de latence) pour les fichiers du journal des transactions en priorité par rapport aux fichiers de données.

SQL Server Transaction Log Architecture and Management Guide - Write-ahead logging
Database Checkpoints (SQL Server)
Pages and Extents Architecture Guide

Edit: Au final, j'explique la même chose que la citation d'SQLPro plus haut.

Pour aller plus loin:
A SQL Server DBA myth a day: (15/30) checkpoint only writes pages from committed transactions
How do checkpoints work and what gets logged

Bien lire les réponses antérieures à mon post, avant de s'engouffrer dans ce que je vais dire.

Précision : Dans le monde de la base de données, si on veux les mécanismes d'annulation multiples (comme dans les outils bureautiques par exemple) il faut le prévoir et le "programmer" de conséquence. Le rollback est limité à la durée de vie de la transaction puisque c'est une façon de la conclure.
La 6ieme forme normale peut être une structuration pour ça (mais ça nous éloigne fort de mon propos).

A la différence d'Oracle, MSSQL ne conserve pas en mémoire les "images avant" des données lors de la modification de celles-ci. C'est peut être ce comportement dont t'as entendu parler.
A la place MSSQL utilise :
* des verrous de lecture en plus des verrous d'écriture pour gérer, conformément à la norme, les attendus des "niveaux d'isolation".
* le fichier journal pour faire les rollback (si on chipote, il y a bien eu duplication de la donnée en mémoire entre le moment où débute la modification de la donnée préexistante en mémoire et l'écriture effective dans le journal)

Ce fonctionnement est particulièrement efficace quand il est bien compris car il réclame moins de CPU et de mémoire au dépend d'une disponibilité plus faible.

Or l'accroissement des parts de marchés de MSSQL au détriment d'Oracle a eu pour conséquence d'intégrer des développeurs PL dans les équipes Transact.
D'autre part certains scénario sont inadaptés à cette implémentation.
Par exemple une base où la donnée entrante a une courte durée d'utilité. Si de nombreuses modifications y sont apportées dans l'intervalle, les tentatives de lectures sont d'autant plus importantes et ... nombreuses. Le volume entrant accroit le besoin de vision d'ensemble et donc les requêtes d'analyse pour faire du reporting réactif.
Bref le mécanisme de verrouillage devient trop pénalisant dans ce scénario particulier.

Microsoft a implémenté "une façon proche de celle d'Oracle" de traiter les images avant des données.
On va retrouver ça via les "isolations d'instantanés" (Snapshot isolation).
Ces 'snapshots' répondent au 2 points précédents.
***au passage pointons qu'Oracle n'a pas fait le travail en miroir c'est à dire de pouvoir se passer des 'rollback segments'. A ma connaissance du moins.
Du coup, si cette option (2 implémentations possibles) est prise, les 'valeurs avant' seront bien en TEMPDB.
Une fois qu'on a dit ça la complexité augmente car il faut comprendre le besoin en volume pour administrer correctement TEMPDB et les perf associées.
***au passage on notera que la différence d’implémentation ne permet pas les opérations de FLASHBACK sur MSSQL

Mesurer la différence de performance des 2 approches peut être faite en capturant l'activité existante sur une base de données dont on a pu restaurer un backup complet afin d'y rejouer l'activité en ne faisant varier QUE le niveau d'isolation. Puis viendra le temps de faire comprendre les résultats pour qu'un arbitrage puisse être pris de manière éclairé.

Comme quoi une question qui peut paraitre simple peu nous amener loin.
Bien venu sur MSSQL.

Citation:

---

Envoyé par jporet

Merci beaucoup pour votre réponse

Quand on fait un delete/update/insert ça vient écrire dans le fichier journal les lignes à supprimer/modifier/insérer et à un moment donné les suppressions /modifications/creations vont se faire dans le fichier data.
La base tempdb n'intervient jamais dans ce genre de cas ?

---

tempdb est une base qui stocke des données temporaires donc volatile. Elle ne sert JAMAIS pour gérer les transaction. Il se peut qu'une requête DELETE utilise une table temporaire, lorsque le DELETE se fait via une sous requête complexe nécessitant de manipuler beaucoup de lignes. Alors l'optimiseur peut choisir de mettre une partie des données dans une table de travail (worktable) ou un fichier de travail (workfile) généré dans la tempdb, mais ceci uniquement afin de soulager le cache. En effet, si votre cache fait 32 go et qu'à un moment donné un résultat intermédiaire dans le calcul de vos données à supprimer, nécessite 10 Go de stockage, il y a fort à parier qu'il utilisera une table temporaire plutôt que la RAM, car cela ferait dégager du cache 10 go de données de tables de production !

Citation:

---

Ce qui déclenche la bascule dans le fichier data c'est la fin de la transaction qu'elle soit implicite ou pas ?

---

Non. Les données sont écrites de manière asynchrone, c'est à dire quand le moteur le veut…. En effet il n'y a aucune urgence à écrire les données dans les fichiers de données, car les nouvelles informations sont en mémoire, et dans le pire des cas, crash machine par exemple, le moteur SQL se servira du journal de transaction pour reproduire les nouvelles données en partant des anciennes qu'il a stockées et de la requête de modification qu'il a conservé.


Lisez le livre que nous avons écrit à ce sujet :
Pièce jointe 450949

A +

Citation:

---

Envoyé par Michel.Priori

Bien lire les réponses antérieures à mon post, avant de s'engouffrer dans ce que je vais dire.

Précision : Dans le monde de la base de données, si on veux les mécanismes d'annulation multiples (comme dans les outils bureautiques par exemple) il faut le prévoir et le "programmer" de conséquence. Le rollback est limité à la durée de vie de la transaction puisque c'est une façon de la conclure.
La 6ieme forme normale peut être une structuration pour ça (mais ça nous éloigne fort de mon propos).

A la différence d'Oracle, MSSQL ne conserve pas en mémoire les "images avant" des données lors de la modification de celles-ci. C'est peut être ce comportement dont t'as entendu parler.
A la place MSSQL utilise :
* des verrous de lecture en plus des verrous d'écriture pour gérer, conformément à la norme, les attendus des "niveaux d'isolation".
* le fichier journal pour faire les rollback (si on chipote, il y a bien eu duplication de la donnée en mémoire entre le moment où débute la modification de la donnée préexistante en mémoire et l'écriture effective dans le journal)

---

Totalement faux ! SQL Server conserve les images avant… La meilleure preuve est que tu peut reconstituer les données perdues par un DELETE par exemple… en utilisant le journal de transaction !

Citation:

---

Ce fonctionnement est particulièrement efficace quand il est bien compris car il réclame moins de CPU et de mémoire au dépend d'une disponibilité plus faible.

Or l'accroissement des parts de marchés de MSSQL au détriment d'Oracle a eu pour conséquence d'intégrer des développeurs PL dans les équipes Transact.
D'autre part certains scénario sont inadaptés à cette implémentation.
Par exemple une base où la donnée entrante a une courte durée d'utilité. Si de nombreuses modifications y sont apportées dans l'intervalle, les tentatives de lectures sont d'autant plus importantes et ... nombreuses. Le volume entrant accroit le besoin de vision d'ensemble et donc les requêtes d'analyse pour faire du reporting réactif.
Bref le mécanisme de verrouillage devient trop pénalisant dans ce scénario particulier.

Microsoft a implémenté "une façon proche de celle d'Oracle" de traiter les images avant des données.
On va retrouver ça via les "isolations d'instantanés" (Snapshot isolation).

---

Tu mélange deux choses qui n'ont rien à voir : la journalisation des transactions et la gestion des niveau d'isolation par ,verrouillage optimiste ou pessimiste… Je suis d'ailleurs en train d'écrire un article là dessus car les gens n'y comprennent généralement pas grand chose !

Citation:

---

Ces 'snapshots' répondent au 2 points précédents.
***au passage pointons qu'Oracle n'a pas fait le travail en miroir c'est à dire de pouvoir se passer des 'rollback segments'. A ma connaissance du moins.
Du coup, si cette option (2 implémentations possibles) est prise, les 'valeurs avant' seront bien en TEMPDB.
Une fois qu'on a dit ça la complexité augmente car il faut comprendre le besoin en volume pour administrer correctement TEMPDB et les perf associées.
***au passage on notera que la différence d’implémentation ne permet pas les opérations de FLASHBACK sur MSSQL

Mesurer la différence de performance des 2 approches peut être faite en capturant l'activité existante sur une base de données dont on a pu restaurer un backup complet afin d'y rejouer l'activité en ne faisant varier QUE le niveau d'isolation. Puis viendra le temps de faire comprendre les résultats pour qu'un arbitrage puisse être pris de manière éclairé.

Comme quoi une question qui peut paraitre simple peu nous amener loin.
Bien venu sur MSSQL.

---

A +

Bonsoir Frédérique,
Toujours prompt à répondre à ce que je vois.
Bon la remarque initiale sur laquelle j'ai rebondi était de savoir si TempDB était utilisée pendant une transaction.
J'avoue, à la relecture de mon post, que je n'ai pas été clair sur mon objectif.

Ce qui est bien c'est qu'on est d'accord :

Citation:

---

SQL Server conserve les images avant… La meilleure preuve est que tu peut reconstituer les données perdues par un DELETE par exemple… en utilisant le journal de transaction !

---

est la même chose que :

Citation:

---

* le fichier journal pour faire les rollback

---

Bien sûr que SQL sait faire des rollback. Mais il n'utilise pas pour ça un "rollback segment", impliquant la gestion Oracle des segments et donc une certaine persistance en mémoire (au delà de la transaction elle même).
Je suis sûr que tu l'avais su faire la distinction en remarquant ces quelques mots :

Citation:

---

MSSQL ne conserve pas en mémoire

---

Je sais que tu n'as pas été dupe.
Pas toi.


Pour la suite, désolé je n'ai pas compris la remarque :

Citation:

---

Tu mélange deux choses qui n'ont rien à voir : la journalisation des transactions et la gestion des niveau d'isolation par ,verrouillage optimiste ou pessimiste… Je suis d'ailleurs en train d'écrire un article là dessus car les gens n'y comprennent généralement pas grand chose !

---

C'est par rapport au message du dessus, du dessous, les 2 ??? Quelle est ton analyse de texte te permettant de dire que j'ai fait une confusion entre ces 4 éléments ?
C'est d'autant plus étrange que je n'emploie jamais "optimiste" ou "pessimiste", ni même verrouillage...

Je pense qu'on s'éloigne de la demande initiale.
C'est vrai, c'est moi qui ait commencé :aie:

Encore une fois désolé de ne pas avoir été clair. Mon but initial était de dire que :
1- oui TempDB pouvait être mis à contribution pendant une transaction et que ceci l'était de manière flagrante via l'isolation de snapshot.
2- ce n'est pas le comportement comportement par défaut
3- que les posts précédents décrivent la "normalité"
et je valide complètement ce qui a été dit :
4- tempDB ne sert jamais à GERER une transaction
5- le journal est LA ressource pour gérer les transactions -et plus particulièrement le rollback- dans SQL server

D'ailleurs je me demande si en cas de rollback et en isolation d’instantané, MSSQL utilise le journal -comme d'hab- ou le versionning ?

Citation:

---

Envoyé par Michel.Priori

Bien sûr que SQL sait faire des rollback. Mais il n'utilise pas pour ça un "rollback segment", impliquant la gestion Oracle des segments et donc une certaine persistance en mémoire (au delà de la transaction elle même).

---

Non, non, tu n'y est pas. Le "rollback segment" existe bien. Oracle, SQL Server, IBM Db2 et bien d'autres encore utilisent l'algorithme ARIES comportant tous les mêmes parties redo et undo structurés logiquement à peu de chose près, de la même manière. La seul différence est que Oracle le fait en 2 fichiers tandique que SQL Server le fait en un seul !
https://bytes.com/topic/sql-server/a...llback-segment
https://archive.sap.com/discussions/thread/1753502
https://www.brentozar.com/archive/20...ql-server-dba/

Citation:

---

...

D'ailleurs je me demande si en cas de rollback et en isolation d’instantané, MSSQL utilise le journal -comme d'hab- ou le versionning ?

---

Le journal

A +