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.

2. Isolationsstufenexperiment

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

🎜>

	Transaktion A READ-UNCOMMITTED	Transaktion READB - VERPFLICHTET,	Transaktion C-1 REPEATABLE-READ	Transaktion C-2 REPEATABLE-READ	Transaktion D SERIALIZIERBAR
Autocommit =0 setzen;
Transaktion starten ;				Transaktion starten;
in t(a)-Werte einfügen (4);
	wählen Sie * aus t; 1,2,3,4 (Dirty Read: Daten in nicht festgeschriebenen Transaktionen lesen)	wählen Sie * von t; 1,2,3 (Dirty Reads lösen)	select * from t; 1, 2,3	wähle * aus t; 1,2,3	wähle * aus t; 1, 2,3
commit;			select * from t:	1,2,3, 4	wählen Sie * aus t: 1,2,3,4	wählen Sie * aus t: 1,2,3,4 (nicht in derselben Transaktion wie oben, daher erfolgt der Lesevorgang spätestens nach der Übermittlung der Transaktion, sodass er gelesen werden kann 4)	select * von t: 1,2,3 (Wiederholtes Lesen: Da es sich um dieselbe Transaktion wie oben handelt, werden nur die Daten der Transaktionsstarttransaktion gelesen, also wiederholtes Lesen)	wähle * aus t: 1,2,3,4
				festschreiben (Transaktion einreichen, das Folgende ist eine neue Transaktion, sodass Sie nach dem Absenden der Transaktion die neuesten Daten lesen können)
				wählen Sie * aus t: 1,2,3,4
READ-UNCOMMITTED generiert Dirty Reads und ist daher selten auf tatsächliche Szenarien anwendbar es wird grundsätzlich nicht genutzt.

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

事务A	事务B READ-COMMITTED	事务C REPEATABLE-READ
set autocommit =0;
start transaction ;	start transaction;	start transaction;
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只是确保读取最新事务已经提交的数据。

事务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

    td>        

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;

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

事务A SERIALIZABLE	事务B READ-UNCOMMITTED	事务C READ-COMMITTED,	事务D REPEATABLE-READ	事务E SERIALIZABLE
set autocommit =0;
start transaction ;			start transaction;
select a from t union all select sleep(1000) from dual;
	insert into t(a)values(5);	insert into t(a)values(5);	insert into t(a)values(5);	insert into t(a)values(5);
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	SERIALIZABLE 串行化执行，导致所有其他事务不得不等待事务A结束才行可以执行，这里特意使用了sleep函数，直接导致事务B,C,D,E等待事务A持有释放的锁。由于我sleep了1000秒，而innodb_lock_wait_timeout为120s。所以120s到了就报错HY000错误。
SERIALIZABLE ist ein sehr strenger Serialisierungsausführungsmodus. Unabhängig davon, ob es sich um Lesen oder Schreiben handelt, wirkt es sich auf andere Transaktionen aus, die dieselbe Tabelle lesen. Es handelt sich um eine strikte exklusive Lese-/Schreibsperre auf Tabellenebene. Außerdem gehen die Vorteile der Innodb-Engine verloren. Praktische Anwendungen gibt es nur wenige.

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:

SitzungA	Sitzung B
Transaktion starten ;	Transaktion starten ;
SELECT * FROM t_bitfly; leerer Satz
	INSERT INTO t_bitfly VALUES (1, 'a');COMMIT;
SELECT * FROM t_bitfly; | leeres Set
INSERT INTO t_bitfly VALUES (1, 'a'); |ERROR 1062 (23000): |Doppelter Eintrag '1' für Schlüssel 1 ( Du hast mir gerade deutlich gesagt, dass es keine solche Aufzeichnung gibt.)	Ich

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	Session B
start transaction ;	start transaction ;
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly VALUES (2, 'b');
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	COMMIT;
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
UPDATE t_bitfly SET value='z'; | Rows matched: 2  Changed:2  Warnings: 0 (怎么多出来一行)
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |z     | | |    2 |z     | | +------+-------+

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：

Session A	Session B
start transaction ;	start transaction ;
SELECT * FROM t_bitfly  WHERE id<=1  FOR UPDATE; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly   VALUES (2, 'b'); | Query OK, 1 row affected
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly VALUES (0, '0'); |  (waiting for lock ... |   then timeout) ERROR 1205 (HY000):Lock wait timeout exceeded; |try restarting transaction
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	COMMIT;
SELECT * FROM t_bitfly; | +------+-------+ | | id   | Wert | | +------+-------+ | |    1 |a     | | +------+-------+

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:

Sitzung A	Sitzung B
Transaktion starten ;	Transaktion starten ;
SELECT * FROM t_bitfly; | +------+-------+ | | id   | Wert | | +------+-------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly   VALUES (2, 'b');
	COMMIT;
SELECT * FROM t_bitfly; | +------+-------+ | | id   | Wert | | +------+-------+ | |    1 |a     | | +------+-------+
SELECT * FROM t_bitfly SPERREN IM TEILUNGSMODUS; | +----+-------+ | | id | Wert | | +----+-------+ | |  1 |a     | | |  2 |b     | | +----+-------+
SELECT * FROM t_bitfly FOR UPDATE; | +----+-------+ | | id | Wert | | +----+-------+ | |  1 |a     | | |  2 |b     | | +----+-------+
SELECT * FROM t_bitfly; | +----+-------+ | | id | Wert | | +----+-------+ | |  1 |a     | | +----+-------+

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!