Was sind die vier Transaktionsebenen von MySQL InnoDB und Dirty Reads, Non-Repeated Reads und Phantom Reads?

1. MySQL InnoDB-Transaktionsisolationsstufe Dirty Read, Repeatable Read, Phantom Read

MySQL InnoDB-Transaktionsisolationsstufe hat vier Stufen, der Standardwert ist „REPEATABLE“ READ).

· 1). Eine andere Transaktion hat die Daten geändert, sie jedoch noch nicht übermittelt, und SELECT in dieser Transaktion liest die nicht festgeschriebenen Daten (Dirty Read) ( Die niedrigste Isolationsstufe und hohe Parallelitätsleistung ) .

· 2). Was diese Transaktion liest, sind die neuesten Daten (nachdem andere Transaktionen festgeschrieben wurden). Das Problem besteht darin, dass in derselben Transaktion dasselbe SELECT zweimal unterschiedliche Ergebnisse liest (ohne wiederholtes Lesen). Es kommt zu nicht wiederholbaren Lese- und Phantomleseproblemen (Sperren der gelesenen Zeile)

· 3). In derselben Transaktion ist das Ergebnis von SELECT der Status zum Zeitpunkt des Transaktionsstarts. Daher sind die von derselben SELECT-Operation gelesenen Ergebnisse konsistent. Allerdings wird es eine Phantomlesung geben (wird später erklärt). Es kommt zu Phantom-Lesevorgängen (alle gelesenen Zeilen sind gesperrt).

· 4). Lesevorgänge erwerben implizit gemeinsame Sperren, wodurch ein gegenseitiger Ausschluss (Sperrtabelle) zwischen verschiedenen Transaktionen sichergestellt wird.

‘

Vier Stufen zunehmender Intensität, von denen jede ein Problem löst.

· 1) Schmutziges Lesen. Eine andere Transaktion hat die Daten geändert, sie jedoch noch nicht festgeschrieben, und SELECT in dieser Transaktion liest die nicht festgeschriebenen Daten.

· 2). Kein wiederholtes Lesen. Nachdem Sie den Dirty Read gelöst haben, werden Sie feststellen, dass während der Ausführung derselben Transaktion eine andere Transaktion neue Daten übermittelt hat, sodass die von dieser Transaktion zweimal gelesenen Datenergebnisse inkonsistent sind.

· 3). Es löst das Problem des nicht wiederholten Lesens und stellt sicher, dass in derselben Transaktion die Ergebnisse der Abfrage den Zustand (Konsistenz) zu Beginn der Transaktion aufweisen. Wenn jedoch gleichzeitig eine andere Transaktion neue Daten übermittelt und diese aktualisiert, werden Sie „überrascht“ sein, diese neuen Daten zu entdecken. Es scheint, dass die Daten, die Sie zuvor gelesen haben, eine „Geister“-Illusion sind . .

Konkret:

1). Dirty Read

Unterscheiden Sie zunächst zwischen schmutzigen Seiten und schmutzigen Daten

Mitte. Das Lesen und Ändern von Seiten im Pufferpool ist normal und kann die Effizienz verbessern. Flush kann synchronisiert werden. Schmutzige Daten bedeuten, dass die Transaktion den Zeilendatensatz im Pufferpool geändert, ihn aber noch nicht übermittelt hat! ! ! Wenn zu diesem Zeitpunkt nicht festgeschriebene Zeilendaten im Pufferpool gelesen werden, spricht man von einem Dirty Read, der die Isolation von Transaktionen verletzt. Dirty Reading bedeutet, dass, wenn eine Transaktion auf Daten zugreift und die Daten geändert hat, die Änderung jedoch noch nicht an die Datenbank übermittelt wurde, eine andere Transaktion ebenfalls auf die Daten zugreift und diese dann verwendet.

2). Nicht wiederholbares Lesen

bezieht sich auf das mehrmalige Lesen derselben Daten innerhalb einer Transaktion. Bevor diese Transaktion endet, greift auch eine andere Transaktion auf dieselben Daten zu. Dann wurde zwischen den beiden Lesevorgängen der Daten in der ersten Transaktion die zweite Transaktion aufgrund der Änderungen der zweiten Transaktion festgeschrieben. Dann können die von der ersten Transaktion zweimal gelesenen Daten unterschiedlich sein. Auf diese Weise sind die innerhalb einer Transaktion zweimal gelesenen Daten unterschiedlich und werden daher als nicht wiederholbares Lesen bezeichnet. Beispielsweise liest ein Redakteur dasselbe Dokument zweimal, aber zwischen den Lesevorgängen schreibt der Autor das Dokument neu. Wenn der Redakteur das Dokument ein zweites Mal liest, hat sich das Dokument geändert. Rohlesevorgänge sind nicht wiederholbar. Dieses Problem kann vermieden werden, wenn Redakteure das Dokument erst lesen können, nachdem der Autor es fertig geschrieben hat

3). auf ein Phänomen, das auftritt, wenn Transaktionen nicht unabhängig voneinander ausgeführt werden. Beispielsweise ändert die erste Transaktion die Daten in einer Tabelle, und diese Änderung betrifft alle Datenzeilen in der Tabelle. Gleichzeitig werden durch die zweite Transaktion auch die Daten in dieser Tabelle geändert. Durch diese Änderung wird eine Zeile mit neuen Daten in die Tabelle eingefügt. Dann wird der Benutzer, der die erste Transaktion ausführt, in Zukunft feststellen, dass die Tabelle immer noch unveränderte Datenzeilen enthält, als ob eine Halluzination aufgetreten wäre. Beispielsweise ändert ein Redakteur ein von einem Autor eingereichtes Dokument, aber wenn die Produktion seine Änderungen in die Masterkopie des Dokuments einfügt, stellt sich heraus, dass der Autor dem Dokument neues, unbearbeitetes Material hinzugefügt hat. Dieses Problem kann vermieden werden, wenn niemand neues Material zum Dokument hinzufügen kann, bis die Redakteure und die Produktionsabteilung die Arbeit am Originaldokument abgeschlossen haben.

Das folgende Experiment basiert auf dem Blogger MySQL Server 5.6

Erstellen Sie zunächst eine Tabelle wie folgt:

USE test;  
CREATE TABLE `t` (  
  
  `a` int(11) NOT NULL PRIMARY KEY  
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.1. Erklären Sie Dirty-Read- und wiederholbare Leseprobleme

2.2、实验二:测试READ-COMMITTED与REPEATABLE-READ

事务B LESEN -COMMITTED

事务C REPEATABLE- LESEN

set autocommit =0;

Transaktion starten ;

Transaktion starten;

Transaktion starten;

insert into t(a)values(4);

select * from t;

1,2,3

select * from t;

1,2,3

commit;

select * from t:

1,2,3,4

select * from t:

1,2,3复读）

commit（提交事务，下面的就是一个新的事务，所以可以读到事务提交以后的最新数据）

select * from t:

1,2,3,4

REPEATABLE-READ“以后, 即使其他事务已经提交新的数据, 同一个事务中再次select也并不会被读取）。

READ-COMMITTED只是确保读取最新事务已经提交的数据.

当然数据的可见性都是对不同事务来说的，同一个事务，都是可以读到此事务中最新数据的。如下，

1. start transaction;  
   insert into t(a)values(4);  
   select *from t;    
   1,2,3,4;  
   insert into t(a)values(5);  
   select *from t;  
   1,2,3,4,5;

   Nach dem Login kopieren

2.3、实验三：测试SERIALIZABLE事务对其他的影响

2.4. Experiment 4: Phantom Read

Einige Artikel schreiben, dass das wiederholbare Lesen von InnoDB „Phantom Read“ vermeidet. Führen Sie ein Experiment durch: (Bei allen folgenden Experimenten sollten Sie auf die Speicher-Engine und die Isolationsstufe achten)

1. ERSTELLEN TABLE `t_bitfly` (

2. `id` bigint( 20) NICHT NULL Standard '0' ,

3. `value` varchar(32) Standard NULL,

4. PRIMÄR KEY (`id`)

5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7. select @@global.tx_isolation, @@tx_isolation;  

8. +-----------------------+- --+  

9. | @@global.tx_isolation | @@tx_isolation  |  

10. +-----------------------+- --+  

11. | WIEDERHOLBAR-LESEN       | WIEDERHOLBAR-LESEN |  

12. +-----------------------+- --+  

实验4-1:

Auf diese Weise kommt es zu Phantomlesungen und ich denke, dass es keine gibt Daten in der Tabelle, aber tatsächlich waren die Daten bereits vorhanden. Nach dem Absenden stellte ich fest, dass die Daten in Konflikt standen.

Experiment 4-2:

Session A

Sitzung B

transaktion starten ;

transaktion starten ;

SELECT * FROM t_bitfly;| +------+-------+| | id | value || + ------+-------+|. 1 |a ||

INSERT INTO t_bitfly VALUES (2, 'b');

SELECT * FROM t_bitfly;| +------+-------+| id |.|. --+

COMMIT;

SELECT * FROM t_bitfly;| +------+-------+| | id | value || +--- ---+------+|. 1 |a || +------+

UPDATE t_bitfly SET value='z';|. Übereinstimmende Zeilen: 2 Geändert:2 Warnungen: 0

(So erhalten Sie eine zusätzliche Zeile)

SELECT * FROM t_bitfly;| +------+-------+| | id | value ||. +------+------+| 2 |z |

Eine Zeile wird zum ersten Mal in dieser Transaktion gelesen. Nach einer Aktualisierung werden die in einer anderen Transaktion übermittelten Daten angezeigt. Es kann auch als eine Art Phantomlesung angesehen werden.

Mit Erklärung

Was ist also der Grund, warum InnoDB darauf hingewiesen hat, dass Phantomlesevorgänge vermieden werden können?

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

Standardmäßig arbeitet InnoDB in der Transaktionsisolationsstufe REPEATABLE READ und mit deaktivierter Systemvariable innodb_locks_unsafe_for_binlog. In diesem Fall verwendet InnoDB Next-Key-Sperren für Suchen und Index-Scans, was Phantomzeilen verhindert (siehe Abschnitt 13.6.8.5, „Vermeiden des Phantomproblems durch Sperren der nächsten Taste“).

Das vorbereitete Verständnis ist, dass, wenn die Isolationsstufe wiederholbares Lesen ist und innodb_locks_unsafe_for_binlog deaktiviert ist, der Index in Next-Keylocks durchsucht wird kann verwendet werden, um Phantom-Lesevorgänge zu vermeiden.

Der entscheidende Punkt ist: Fügt InnoDB standardmäßig auch Next-Key-Sperren zu einer normalen Abfrage hinzu oder muss die Anwendung die Sperren selbst hinzufügen? Wenn Sie diesen Satz gerade lesen, denken Sie vielleicht, dass InnoDB auch Sperren zu normalen Abfragen hinzufügt. Wenn ja, was ist der Unterschied zwischen dieser und der Serialisierung (SERIALIZABLE)?

Es gibt einen weiteren Absatz im MySQL-Handbuch:

13.2.8.5. Vermeiden des Phantomproblems durch Sperren der nächsten Taste (http://dev.mysql.com/doc/refman/5.0/ en/ innodb-next-key-locking.html)

Um Phantome zu verhindern, InnoDB verwendet einen Algorithmus namens next-key Sperrung, die die Indexzeilensperre mit der Lückensperre kombiniert.

Sie können die Next-Key-Sperre verwenden, um eine Eindeutigkeitsprüfung in Ihrer Anwendung zu implementieren: Wenn Sie Ihre Daten im Freigabemodus lesen und kein Duplikat für sehen eine Zeile, die Sie einfügen möchten, dann können Sie Ihre Zeile sicher einfügen und wissen, dass die nächste Tastensperre auf der eingestellt ist Der Erfolg Ihrer Zeile während des Lesevorgangs verhindert, dass irgendjemand ein Duplikat für Ihre Zeile einfügt. Mit der Next-Key-Sperre können Sie also die Nichtexistenz von etwas in Ihrer Tabelle „sperren“.

Mein Verständnis ist Say , InnoDB bietet Next-Key-Sperren, die Anwendung muss diese jedoch selbst sperren. Ein Beispiel finden Sie im Handbuch:

SELECT * FROM child WHERE id> 100 FOR UPDATE;

这样，InnoDB会给id大于100的行（假如child表里有一行id为102），以及100-102，102+的gap都加上锁。

可以使用show engine innodb status来查看是否给表加上了锁。

再看一个实验，要注意，表t_bitfly里的id为主键字段。

实验4-3：

Sie können sehen, dass die mit der ID<=1 hinzugefügte Sperre nur den Bereich der ID<=1 sperrt. Der Datensatz mit der ID 2 kann erfolgreich hinzugefügt werden. Beim Hinzufügen des Datensatzes mit der ID 0 wird auf die Sperre gewartet freigelassen werden.

Angehängter Hinweis:

Detaillierte Erläuterung von Sperren bei wiederholbaren Lesevorgängen im MySQL-Handbuch:

http://dev.mysql.com/doc/refman/5.0 /en /set-transaction.html#isolevel_repeatable-read

Zum Sperren von Lesevorgängen (SELECT mit FOR UPDATE oder LOCK IN SHARE MODE),UPDATE, und DELETE-Anweisungen hängt das Sperren davon ab, ob die Anweisung verwendet wird ein eindeutiger Index mit einer eindeutigen Suchbedingung oder eine Suchbedingung vom Typ Bereich. Für einen eindeutigen Index mit einer eindeutigen Suchbedingung wird InnoDB gesperrt Es wird nur der Indexdatensatz gefunden, nicht die Lücke davor. Bei anderen Suchbedingungen wird InnoDB gesperrt Der Indexbereich wird gescannt, wobei Gap-Sperren oder Next-Key-Sperren (Gap plus Index-Record) verwendet werden, um Einfügungen durch andere Sitzungen in die vom Bereich abgedeckten Lücken zu blockieren.

Konsistenz-Lesen und Commit-Lesen, sehen Sie sich zuerst das Experiment an ,

Experiment 4-4:

Anhang: Wenn Sie normales Lesen verwenden, erhalten Sie konsistente Ergebnisse. Wenn Sie gesperrtes Lesen verwenden, lesen Sie das „letzte“ „Commit“-Leseergebnis.

selbst, wiederholbares Lesen und festgeschriebenes Lesen sind widersprüchlich. Wenn in derselben Transaktion ein wiederholbarer Lesevorgang garantiert ist, werden die Festschreibungen anderer Transaktionen nicht angezeigt, was gegen den festgeschriebenen Lesevorgang verstößt.

Man kann sagen, dass InnoDB einen solchen Mechanismus bereitstellt. In der standardmäßigen wiederholbaren Leseisolationsstufe können Sie gesperrtes Lesen verwenden, um die neuesten Daten abzufragen.

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

Wenn Sie den „frischesten“ Zustand der Datenbank sehen möchten , sollten Sie entweder die Isolationsstufe READ COMMITTED oder einen Sperr-Read verwenden:
SELECT * FROM t_bitfly LOCK IN SHARE MODE;

------

3. Zusammenfassung

Fazit: Die Standardisolationsstufe von MySQL InnoDB-Transaktionen ist wiederholbares Lesen, was die Vermeidung von Phantomlesevorgängen nicht garantiert. Um dies sicherzustellen, muss die Anwendung gesperrte Lesevorgänge verwenden. Der für diesen Sperrgrad verwendete Mechanismus sind Next-Key-Schlösser.

Das obige ist der detaillierte Inhalt vonWas sind die vier Transaktionsebenen von MySQL InnoDB und Dirty Reads, Non-Repeated Reads und Phantom Reads?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!