Relation entre les niveaux d'isolation des transactions et les verrous sur les tables

Question

J'ai lu qu'il y avait 4 niveaux d'isolement :

Isolation Level       Dirty Read    Nonrepeatable Read  Phantom Read  
READ UNCOMMITTED      Permitted       Permitted           Permitted
READ COMMITTED              --        Permitted           Permitted
REPEATABLE READ             --             --             Permitted
SERIALIZABLE                --             --              --

Je veux comprendre le verrou que chaque isolation de transaction prend sur la table

READ UNCOMMITTED - no lock on table
READ COMMITTED - lock on committed data
REPEATABLE READ - lock on block of sql(which is selected by using select query)
SERIALIZABLE - lock on full table(on which Select query is fired)

Voici les trois phénomènes qui peuvent se produire lors de l'isolement d'une transaction
Dirty Read - aucune serrure
Lecture non répétable - pas de lecture sale car verrouillage sur les données engagées
Phantom Read - verrouillage sur le bloc de sql (qui est sélectionné en utilisant la requête select)

Je veux comprendre où nous définissons ces niveaux d'isolation : seulement au niveau de jdbc/hibernate ou dans la base de données également.

PS : J'ai parcouru les liens dans Niveaux d'isolement dans oracle mais ils ont l'air maladroits et parlent d'une base de données spécifique.

Answer 1

Je veux comprendre le verrou que chaque isolation de transaction prend sur la table

Par exemple, vous avez 3 processus concurrents A, B et C. A démarre une transaction, écrit des données et fait un commit/rollback (en fonction des résultats). B exécute simplement une SELECT pour lire les données. C lit et met à jour des données. Tous ces processus travaillent sur la même table T.

• LIRE SANS ENGAGEMENT - aucun verrou sur la table. Vous pouvez lire des données dans la table tout en écrivant dessus. Cela signifie que A écrit des données (non engagées) et que B peut lire ces données non engagées et les utiliser (dans n'importe quel but). Si A exécute un rollback, B a toujours lu les données et les a utilisées. C'est la façon la plus rapide mais la moins sûre de travailler avec des données, car elle peut conduire à des trous de données dans des tables qui ne sont pas physiquement liées (oui, deux tables peuvent être logiquement mais pas physiquement liées dans des applications réelles =\).
• READ COMMITTED - verrouiller les données validées. Vous pouvez lire les données qui ont été seulement livrées. Cela signifie que A écrit des données et que B ne peut pas lire les données enregistrées par A jusqu'à ce que A exécute un commit. Le problème ici est que C peut mettre à jour les données qui ont été lues et utilisées sur B et le client B n'aura pas les données mises à jour.
• LECTURE RÉPÉTÉE - verrouiller un bloc de SQL (qui est sélectionné en utilisant la requête select). Cela signifie que B lit les données sous certaines conditions, à savoir WHERE aField > 10 AND aField < 20 , A insère des données où aField est comprise entre 10 et 20, alors B lit à nouveau les données et obtient un résultat différent.
• SERIALIZABLE - verrouiller une table complète (sur laquelle la requête Select est lancée). Cela signifie que B lit les données et aucune autre transaction ne peut modifier les données sur la table. C'est la manière la plus sûre mais la plus lente de travailler avec des données. De plus, comme une simple opération de lecture verrouille la table Si l'utilisateur X veut connaître les factures du jour et que l'utilisateur Y veut créer une nouvelle facture, alors pendant que X lit les factures, Y ne peut pas ajouter de nouvelle facture (et quand il s'agit d'argent, les gens s'énervent, surtout les patrons).

Je veux comprendre où nous définissons ces niveaux d'isolation : seulement au niveau JDBC/hibernate ou dans la base de données également.

Avec JDBC, vous le définissez en utilisant Connection#setTransactionIsolation .

Utilisation d'Hibernate :

<property name="hibernate.connection.isolation">2</property>

Où

• 1 : LECTURE NON ENGAGÉE
• 2 : LECTURE ENGAGÉE
• 4 : LECTURE RÉPÉTÉE
• 8 : SÉRIALISABLE

La configuration d'Hibernate est prise dans aquí (désolé, c'est en espagnol).

À propos, vous pouvez également définir le niveau d'isolement sur les SGBDR :

• Niveau d'isolation MySQL ,
• Niveau d'isolation du serveur SQL
• Niveau d'isolement d'Informix (Note personnelle : je n'oublierai jamais SET ISOLATION TO DIRTY READ phrase.)

et encore et encore...

Answer 2

Comme le dit brb tea, cela dépend de l'implémentation de la base de données et de l'algorithme utilisé : MVCC ou verrouillage à deux phases.

CUBRID (SGBD open source) explique l'idée de ces deux algorithmes :

• Verrouillage biphasé (2PL)

La première est lorsque la transaction T2 tente de modifier l'enregistrement A, elle sait que la transaction T1 a déjà changé l'enregistrement A et attend jusqu'à ce que la transaction T1 soit terminée parce que la transaction T2 ne peut pas savoir si la transaction T1 sera validée ou annulée. retour. Cette méthode est appelée verrouillage en deux phases (2PL).

• Contrôle de concurrence multi-version (MVCC)

L'autre consiste à permettre à chacune d'entre elles, les transactions T1 et T2, de d'avoir leurs propres versions modifiées. Même si la transaction T1 a a changé l'enregistrement A de 1 à 2, la transaction T1 laisse la la valeur originale 1 telle quelle et écrit que la version de la transaction T1 est 2. de l'enregistrement A est 2. Ensuite, la transaction T2 suivante modifie l'enregistrement A de 1 à 3, mais pas de 1 à 3. de 1 à 3, et non de 2 à 4, et écrit que la version de transaction T2 de l'enregistrement A est 3. de l'enregistrement A est 3.

Lorsque la transaction T1 est annulée, il importe peu que la transaction 2, la version de la transaction T1, n'est pas appliquée à l'enregistrement A. Après Après cela, si la transaction T2 est validée, la version 3 de la transaction sera appliquée à l'enregistrement A. Si la transaction T1 est validée avant la transaction T2, l'enregistrement A est modifié en 2, puis en 3 au moment de la validation de la transaction T2. L'état final final de la base de données est identique à celui de l'exécution de chaque transaction indépendamment, sans aucun impact sur les autres transactions. Par conséquent, il satisfait à la propriété ACID. Cette méthode est appelée Contrôle de concurrence multi-version (MVCC).

La MVCC autorise les modifications simultanées au prix d'une augmentation de la charge de travail en mémoire (parce qu'elle doit maintenir différentes versions des mêmes données) et en calcul (au niveau REPETEABLE_READ, vous ne pouvez pas perdre les mises à jour, elle doit donc vérifier les versions des données, comme le fait Hiberate avec Verrouillage Optimistick ).

En 2PL Les niveaux d'isolement des transactions contrôlent les éléments suivants :

• Si des verrous sont pris lorsque les données sont lues, et quel type de verrous sont demandés.

• Combien de temps les verrous de lecture sont maintenus.

• Si une opération de lecture faisant référence à des lignes modifiées par une autre transaction :

  • Bloque jusqu'à ce que le verrou exclusif sur la rangée soit libéré.

  • Récupère la version validée de la ligne qui existait au moment où la déclaration ou la transaction a commencé.

  • Lire la modification des données non engagées.

Le choix d'un niveau d'isolation des transactions n'affecte pas les verrous qui sont acquis pour protéger les modifications de données. Une transaction obtient toujours un verrou exclusif sur les données qu'elle modifie et le conserve jusqu'à la fin de la jusqu'à ce que la transaction soit terminée, quel que soit le niveau d'isolation défini pour cette transaction. Pour les opérations de lecture, les niveaux d'isolation des transactions définissent principalement le niveau de protection contre les effets des modifications apportées par d'autres transactions.

Un niveau d'isolation plus faible augmente la capacité de nombreux utilisateurs à accéder à aux données en même temps, mais augmente le nombre d'effets de concurrence effets tels que les lectures erronées ou les mises à jour perdues, que les utilisateurs peuvent rencontrer.

Des exemples concrets de la relation entre les verrous et les niveaux d'isolement dans SQL Server (utiliser 2PL sauf sur READ_COMMITED avec READ_COMMITTED_SNAPSHOT=ON)

• READ_UNCOMMITED : n'émet pas de verrous partagés pour empêcher les autres transactions de modifier les données lues par la transaction en cours. Les transactions READ UNCOMMITTED ne sont pas non plus bloquées par des verrous exclusifs qui empêcheraient la transaction actuelle de lire des lignes qui ont été modifiées mais non validées par d'autres transactions. [...]

• READ_COMMITED :

  • Si READ_COMMITTED_SNAPSHOT a la valeur OFF (par défaut) : utilise des verrous partagés pour empêcher d'autres transactions de modifier des lignes pendant que la transaction actuelle exécute une opération de lecture. Les verrous partagés bloquent également l'instruction de lecture des rangées modifiées par d'autres transactions jusqu'à ce que l'autre transaction soit terminée. [...] Les verrous de rangée sont libérés avant le traitement de la rangée suivante. [...]
  • Si le paramètre READ_COMMITTED_SNAPSHOT est défini sur ON, le moteur de base de données utilise le versionnage de ligne pour présenter à chaque instruction un instantané transactionnellement cohérent des données telles qu'elles existaient au début de l'instruction. Les verrous ne sont pas utilisés pour protéger les données des mises à jour effectuées par d'autres transactions.

• REPETEABLE_READ : Des verrous partagés sont placés sur toutes les données lues par chaque instruction de la transaction et sont maintenus jusqu'à la fin de la transaction.

• SERIALIZABLE : Les verrous de plage sont placés dans la plage des valeurs de clés qui correspondent aux conditions de recherche de chaque instruction exécutée dans une transaction. [...] Les verrous de plage sont maintenus jusqu'à ce que la transaction soit terminée.

Answer 3

Les verrous sont toujours pris au niveau de la base de données.

Document officiel d'Oracle : - Pour éviter les conflits au cours d'une transaction, un SGBD utilise des verrous, c'est-à-dire des mécanismes permettant de bloquer l'accès des autres aux données auxquelles la transaction accède. (Notez qu'en mode auto-commit, où chaque déclaration est une transaction, les verrous ne sont maintenus que pour une seule déclaration). Une fois qu'un verrou est défini, il reste en vigueur jusqu'à ce que la transaction soit validée ou annulée. Par exemple, un SGBD peut verrouiller une ligne d'une table jusqu'à ce que ses mises à jour aient été validées. L'effet de ce verrou serait d'empêcher un utilisateur d'obtenir une lecture sale, c'est-à-dire de lire une valeur avant qu'elle ne soit permanente. (L'accès à une valeur mise à jour qui n'a pas été validée est considéré comme une lecture sale car il est possible que cette valeur soit ramenée à sa valeur précédente. Si vous lisez une valeur qui est ensuite annulée, vous aurez lu une valeur non valide).

La manière dont les verrous sont définis est déterminée par ce que l'on appelle le niveau d'isolement des transactions, qui peut aller de l'absence totale de transactions à la prise en charge de transactions qui appliquent des règles d'accès très strictes.

Un exemple de niveau d'isolation de transaction est TRANSACTION_READ_COMMITTED, qui ne permet pas d'accéder à une valeur avant qu'elle n'ait été validée. En d'autres termes, si le niveau d'isolation des transactions est défini sur TRANSACTION_READ_COMMITTED, le SGBD n'autorise pas les lectures sales. L'interface Connection comprend cinq valeurs qui représentent les niveaux d'isolation des transactions que vous pouvez utiliser dans JDBC.