Lassen Sie uns mit Ihnen über die Transaktionsisolation in MySQL sprechen

In diesem Artikel erfahren Sie mehr über die Transaktionsisolation in MySQL, stellen die Eigenschaften von Transaktionen, Isolationsstufen, Transaktionsstartmethoden usw. vor. Ich hoffe, dass er für alle hilfreich ist!

Eine Transaktion soll sicherstellen, dass eine Reihe von Datenbankoperationen entweder alle erfolgreich sind oder alle fehlschlagen. In MySQL ist die Transaktionsunterstützung auf der Engine-Ebene implementiert, aber nicht alle Engines unterstützen Transaktionen. Beispielsweise unterstützt die native MyISAM-Engine von MySQL keine Transaktionen. [Verwandte Empfehlungen: MySQL-Tutorial (Video)]

1. Merkmale von Transaktionen

• Atomizität: Alle Vorgänge in einer Transaktion sind entweder vollständig abgeschlossen oder nicht abgeschlossen und enden nicht irgendwo im mittleren Link. Wenn während der Ausführung der Transaktion ein Fehler auftritt, wird sie auf den Zustand vor Beginn der Transaktion zurückgesetzt, als ob die Transaktion nie ausgeführt worden wäre. Konsistenz: Vor Beginn der Transaktion und nach Ende der Transaktion die Integrität der Datenbank wurde nicht zerstört.
Isolation: Die Datenbank ermöglicht das gleichzeitige Lesen, Schreiben und Ändern von Daten. Durch die Isolation können Dateninkonsistenzen aufgrund der gleichzeitigen Ausführung mehrerer Transaktionen verhindert werden Nach Abschluss sind die Datenänderungen dauerhaft und gehen auch bei einem Systemausfall nicht verloren. 2. Isolationsstufe 1. Wenn mehrere Transaktionen gleichzeitig in der Datenbank ausgeführt werden, können fehlerhafte Lesevorgänge, nicht wiederholbare Lesevorgänge und Phantom-Lesevorgänge auftreten
• Dirty Read: Transaktion B liest nicht festgeschriebene Daten von Transaktion A
• Nicht wiederholbares Lesen: Eine Transaktion liest Aktualisierungsdaten, die in einer anderen Transaktion übermittelt wurden

Phantom Read/virtuelles Lesen: Eine Transaktion liest die in einer anderen Transaktion übermittelten Einfügungsdaten

2. Die Isolationsstufe der Transaktion umfasst: nicht festgeschrieben lesen, festgeschrieben lesen, wiederholbar lesen und serialisiert lesen: nicht festgeschrieben lesen: Wenn eine Transaktion noch nicht übermittelt wurde, können die vorgenommenen Änderungen von anderen Transaktionen gesehen werden : Nachdem eine Transaktion übermittelt wurde, werden die von ihr vorgenommenen Änderungen von anderen Transaktionen gesehen (Dirty Reads lösen, Oracles Standardisolationsstufe).

Wiederholbares Lesen: Die während der Ausführung einer Transaktion angezeigten Daten stimmen immer mit den Daten überein, die beim Ausführen angezeigt werden Die Transaktion wird gestartet und nicht festgeschriebene Änderungen sind auch für andere Transaktionen unsichtbar (um schmutzige Lesevorgänge und nicht wiederholbare Lesevorgänge zu lösen, MySQL-Standard-Isolationsstufe).

Serialisierung: Für dieselbe Datensatzzeile wird beim Schreiben eine Schreibsperre hinzugefügt, beim Lesen wird dies der Fall sein Fügen Sie eine Lesesperre hinzu. Wenn ein Lese-/Schreibsperrenkonflikt auftritt, muss die Transaktion, auf die später zugegriffen wird, auf den Abschluss der vorherigen Transaktion warten, bevor sie weiter ausgeführt werden kann (Lösung für schmutzige Lesevorgänge, nicht wiederholbare Lesevorgänge und Phantom-Lesevorgänge). Die Sicherheit wird der Reihe nach übermittelt und die Leistung wird der Reihe nach reduziert
• 3. Angenommen, es gibt nur eine Spalte in der Datentabelle T und der Wert einer Zeile ist 1

• create table T(c int) engine=InnoDB;
  insert into T(c) values(1);

Das Folgende wird in chronologischer Reihenfolge ausgeführt die beiden Transaktionen:

Wenn die Isolationsstufe nicht festgeschrieben gelesen wird, dann ist V1 2. Obwohl Transaktion B zu diesem Zeitpunkt noch nicht eingereicht wurde, wurde das Ergebnis von A gesehen. Sowohl V2 als auch V3 sind 2
• Wenn die Isolationsstufe Read-Commit ist, dann ist V1 1 und V2 2. Die Aktualisierungen von Transaktion B können von A erst gesehen werden, nachdem sie festgeschrieben wurden. V3 ist ebenfalls 2
• Wenn die Isolationsstufe wiederholbares Lesen ist, dann sind V1 und V2 1 und V3 ist 2. Der Grund, warum V2 1 ist, liegt darin, dass die vor und nach der Ausführung der Transaktion angezeigten Daten konsistent sein müssen. Wenn die Isolationsstufe Serialisierung ist, sind die Werte von V1 und V2 1 und V3 2 Nach der Implementierung wird in der Datenbank eine Ansicht erstellt, und das logische Ergebnis der Ansicht hat beim Zugriff Vorrang. Unter der wiederholbaren Leseisolationsstufe wird diese Ansicht beim Start der Transaktion erstellt und während der gesamten Transaktion verwendet. Unter der Lese-Commit-Isolationsstufe wird diese Ansicht am Anfang jeder SQL-Anweisung erstellt. Unter der nicht festgeschriebenen Lese-Isolationsstufe wird der neueste Wert im Datensatz direkt zurückgegeben, ohne das Konzept einer Ansicht; unter der serialisierten Isolationsstufe wird die Sperre direkt verwendet, um parallelen Zugriff zu vermeiden
• 3. Implementierung der Transaktionsisolation (wiederholbar). (als Beispiel lesen)

In MySQL zeichnet jeder Datensatz auch einen Rollback-Vorgang auf, wenn er aktualisiert wird. Der neueste Wert im Datensatz kann durch Zurücksetzen des Werts des vorherigen Status erhalten werden. Angenommen, ein Wert wird der Reihe nach von 1 auf 2, 3 und 4 geändert. Im Rollback-Protokoll wird ein Datensatz ähnlich dem folgenden angezeigt.

Der aktuelle Wert ist 4, aber beim Abfragen dieses Datensatzes haben Transaktionen, die zu unterschiedlichen Zeiten gestartet wurden, unterschiedliche Leseansichten. Wie Sie in der Abbildung sehen können, sind die Werte dieses Datensatzes in den Ansichten A, B und C jeweils 1, 2 und 4. Derselbe Datensatz kann in mehreren Versionen im System vorhanden sein, was die Multi-Version ist. Versionskontrolle (MVCC) der Datenbank. Um für LeseansichtA 1 zu erhalten, müssen Sie alle Rollback-Vorgänge in der Abbildung auf einmal für den aktuellen Wert ausführen, um

zu erhalten. Selbst wenn eine andere Transaktion 4 in 5 ändert, ist diese Transaktion dieselbe wie Leseansicht A, B, Die Transaktionen, die C entsprechen, werden nicht in Konflikt geraten

系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除

四、事务启动的方式

MySQL的事务启动方式有以下几种：

• 显示启动事务语句，begin或start transaction。提交语句是commit，回滚语句是rollback
• set autocommit=0，这个命令将这个线程的自动提交关掉。意味着如果只执行一个select语句，这个事务就启动了，而且不会自动提交事务。这个事务持续存在直到主动执行commit或rollback语句，或者断开连接

建议使用set autocommit=1，通过显示语句的方式来启动事务

可以在information_schema库中的innodb_trx这个表中查询长事务，如下语句查询持续时间超过60s的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

五、事务隔离还是不隔离

下面是一个只有两行的表的初始化语句：

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

事务A、B、C的执行流程如下，采用可重复读隔离级别


begin/start transaction命令：不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动，一致性视图是在执行第一个快照读语句时创建的

start transaction with consistent snapshot命令：马上启动一个事务，一致性视图是在执行这条命令时创建的

按照上图的流程执行，事务B查到的k的值是3，而事务A查到的k的值是1

1、快照在MVCC里是怎么工作的？

在可重复读隔离级别下，事务启动的时候拍了个快照。这个快照是基于整个库的，那么这个快照是如何实现的？

InnoDB里面每个事务有一个唯一的事务ID，叫做transaction id。它在事务开始的时候向InnoDB的事务系统申请，是按申请顺序严格递增的

每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记作row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本，每个版本有自己的row trx_id

下图是一个记录被多个事务连续更新后的状态：



语句更新生成的undo log（回滚日志）就是上图中的是哪个虚线箭头，而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来的

按照可重复读的定义，一个事务启动的时候，能够看到所以已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。在实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前在启动了但还没提交的所有事务ID。数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位就组成了当前事务的一致性视图。而数据的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的

这个视图数组把所有的row trx_id分成了几种不同的情况



对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：

1）如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的

2）如果落在红色部分，表示这个版本是由将来启动的事务生成的，肯定不可见

3）如果落在黄色部分，那就包括两种情况

• 若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见
• 若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见

InnoDB利用了所有数据都有多个版本的这个特性，实现了秒级创建快照的能力

2、为什么事务A的查询语句返回的结果是k=1？

假设：

1.事务A开始时，系统里面只有一个活跃事务ID是99

2.事务A、B、C的版本号分别是100、101、102

3.三个事务开始前，(1,1)这一行数据的row trx_id是90

Auf diese Weise ist das Array von Transaktion A [99.100], das Ansichtsarray von Transaktion B ist [99.100.101] und das Ansichtsarray von Transaktion C ist [99.100.101.102]



Wie Sie auf dem Bild oben sehen können , das erste wirksame Update Es ist Transaktion C, und die Daten wurden von (1,1) in (1,2) geändert. Zu diesem Zeitpunkt ist die Zeile trx_id der neuesten Version dieser Daten 102, und Version 90 ist zu einer historischen Version geworden

Das zweite effektive Update ist Transaktion B, die die Daten von (1,2) auf (1,3) ändert ). Zu diesem Zeitpunkt ist die neueste Version dieser Daten 101 und 102 ist zur historischen Version geworden. Wenn Transaktion A eine Abfrage durchführt, wurde Transaktion B noch nicht übermittelt, aber die von ihr generierte Version (1,3) ist zur aktuellen Version geworden. . Diese Version muss jedoch für Transaktion A unsichtbar sein, sonst wird sie zu einem Dirty Read. Jetzt möchte Transaktion A Daten lesen und ihr Ansichtsarray ist [99,100]. Das Lesen der Daten beginnt ab der aktuellen Version. Daher ist der Datenleseprozess der Transaktion A-Abfrageanweisung wie folgt:

Beim Finden von (1,3) wird festgestellt, dass die Zeile trx_id = 101, die größer als der Hochwasserstand ist, im roten Bereich liegt. und ist unsichtbar
Dann finden Sie die obere A-Historie-Version, auf den ersten Blick, Zeile trx_id=102, die größer als der Hochwasserstand ist, im roten Bereich liegt und unsichtbar ist
• Bei weiterer Suche habe ich schließlich gefunden ( 1,1), dessen Zeile trx_id=90 ist, was kleiner als der niedrige Wasserstand ist. Es ist ersichtlich, dass
• Obwohl diese Datenzeile im Laufe des Zeitraums geändert wurde, egal wann Transaktion A-Abfragen, die Ergebnisse der Anzeige dieser Datenzeile sind konsistent. Wir nennen es konsistente Lesung
• A-Datenversion. Für eine Transaktionsansicht gibt es zusätzlich dazu, dass ihre eigenen Aktualisierungen immer sichtbar sind, drei Situationen:
Version nicht übermittelt, unsichtbare
-Version wurde übermittelt, aber sie wurde übermittelt, nachdem die Ansicht erstellt wurde, unsichtbare
• -Version wurde übermittelt, und sie wurde übermittelt, bevor die Ansicht erstellt wurde. Es ist ersichtlich, dass das Ansichtsarray der Die Abfrageanweisung von Transaktion A wurde generiert, als Transaktion A gestartet wurde. Zu diesem Zeitpunkt:
• (1,3) wurde noch nicht übermittelt und gehört zu Fall 1, unsichtbar
• (1,2) wurde übermittelt, wird aber übermittelt Wird nach der Erstellung des Ansichtsarrays übermittelt, das zu Fall 2 gehört, unsichtbar

(1,1) wird übermittelt, bevor das Ansichtsarray erstellt wird, sichtbar

• 3 Warum gibt die Abfrageanweisung von Transaktion B k=3 zurück? ?

Wenn Transaktion B Daten aktualisieren möchte, kann die historische Version nicht mehr aktualisiert werden, da sonst die Aktualisierung von Transaktion C verloren geht. Daher ist die Menge k = k + 1 der Transaktion B zu diesem Zeitpunkt eine Operation, die auf (1,2) basiert. Die aktualisierten Daten werden zuerst gelesen und dann geschrieben, und dieser Lesevorgang kann nur den aktuellen Wert lesen lesen. Wenn die SELECT-Anweisung zusätzlich zur Update-Anweisung gesperrt ist, wird sie auch aktuell gelesen. Angenommen, Transaktion C wird nicht sofort übermittelt, sondern wird zur folgenden Transaktion C‘.

In der obigen Abbildung wurde Transaktion C nicht unmittelbar nach der Aktualisierung übermittelt. Vor der Übermittlung wurde zuerst die Aktualisierungsanweisung von Transaktion B initiiert. Obwohl Transaktion C noch nicht übermittelt wurde, wurde die Version (1,2) generiert und ist die neueste Version. Zu diesem Zeitpunkt handelt es sich um ein zweistufiges Sperrprotokoll, und Transaktion C wurde nicht übermittelt (1,2) Die Schreibsperre für diese Version wurde noch nicht freigegeben. Transaktion B ist der aktuelle Lesevorgang, sie muss die neueste Version lesen und gesperrt sein, daher muss sie warten, bis Transaktion C die Sperre aufhebt, bevor sie mit dem aktuellen Lesevorgang fortfahren kann. 7. Transaktion Verfügbarkeit Wie wird die Fähigkeit zum Wiederholen des Lesens erreicht?



Der Kern des wiederholbaren Lesens ist das konsistente Lesen. Wenn eine Transaktion Daten aktualisiert, kann nur der aktuelle Lesevorgang verwendet werden. Wenn die Zeilensperre des aktuellen Datensatzes durch andere Transaktionen belegt ist, müssen Sie die Sperre eingeben. Die Logik des Lese-Commits ähnelt der Logik des wiederholbaren Lesens. Der Hauptunterschied zwischen ihnen ist:

Unter dem wiederholbaren Leseisolationsstufe: Sie müssen nur zu Beginn der Transaktion eine konsistente Ansicht erstellen, und dann teilen andere Abfragen in der Transaktion diese konsistente Ansicht. Unter der Lese-Commit-Isolationsstufe wird vor jeder wiederholt eine neue Ansicht berechnet Anweisung wird ausgeführt


Weitere Informationen zum Programmieren finden Sie unter: Programmiervideo
! !

Das obige ist der detaillierte Inhalt vonLassen Sie uns mit Ihnen über die Transaktionsisolation in MySQL sprechen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!