In der Spalte „MySQL-Tutorial“ werden MySQL-bezogene Transaktionen, Isolationsstufen und MVCC vorgestellt.
Der vierte Teil der MySQL-Reihe, der Hauptinhalt sind Transaktionen, einschließlich Transaktions-ACID-Eigenschaften, Isolationsstufen, Verständnis für schmutzige Lesevorgänge, nicht wiederholbare Lesevorgänge, Phantom-Lesevorgänge und Multi-Version-Parallelitätskontrolle (MVCC). ). Transaction kann garantieren, dass ein unteilbarer Satz atomarer Operationen entweder alle oder keine davon ausgeführt wird. Unter den in MySQL häufig verwendeten Speicher-Engines unterstützt InnoDB Transaktionen, die native MyISAM-Engine unterstützt jedoch keine Transaktionen. In diesem Artikel werden, sofern nicht anders angegeben, die folgenden Datentabellen und Daten verwendet: CREATE TABLE `user` ( `id` int(11) DEFAULT NULL, `name` varchar(12) DEFAULT NULL) ENGINE = InnoDB;insert into user values(1, '刺猬');复制代码
1 Die vier Hauptmerkmale von ACID
Um die Eigenschaften von ACID im Detail zu erklären, stellen wir uns zunächst ein Szenario vor: Ich überweise Ihnen 100 Yuan.
Angenommen, dieser Vorgang kann in die folgenden Schritte unterteilt werden (vorausgesetzt, dass mein und Ihr Kontostand beide 100 Yuan betragen):
Meinen Kontostand prüfenMein Konto belastet 100 Yuan100 Yuan startet die Überweisung Überprüfen Ihr Kontostand100 Yuan wurden Ihrem Konto gutgeschrieben同时,事务的一致性要求符合开发人员定义的约束,如金额大于0、身高大于0等。
在上述的转账场景中,一致性能够保证最终执行完整个转账操作后,我账户的扣款金额与你账户到账金额是一致的,同时如果我和你的账户余额不满足金额的约束(如小于0),整个事务会回滚。
事务的隔离性是指:在一次状态转换过程中不会受到其他状态转换的影响。
假设我和你都有100元,我发起两次转账,转账金额都是50元,下面使用伪代码来表示的操作步骤:
read my
my=my-50
read yours
yours=yours+50
如果未保证隔离性就可能发生下面的情况:
时刻 | 第一次转账 | 第二次转账 | 我的账户余额 | 你的账户余额 |
---|---|---|---|---|
1 | read my(100) | my=100 | yours=100 | |
2 | read my(100) | my=100 | yours=100 | |
3 | my=my-50=100-50=50 | my=50 | yours=100 | |
4 | read yours(100) | my=my-50=100-50=50 | my=50 | yours=100 |
5 | yours=yours+50=100+50=150 | my=50 | yours=150 | |
6 | read yours(150) | my=50 | yours=150 | |
7 | yours=yours+50=150+50=200 | my=50 | yours=200 | |
7 | end | end | my=50 | yours=200 |
两次转账后,最终的结果是我的账户余额为50元,你的账户余额为200元,这显然是不对的。
而如果在保证事务隔离性的情况下,就不会发生上面的情况,损失的只是一定程度上的一致性。
事务的持久性是指:事务在提交以后,它所做的修改就会被永久保存到数据库。
在上述的转账场景中,持久性就保证了在转账成功之后,我的账户余额为0,你的账户余额为200。
在 MySQL 中,我们可以通过 begin 或 start transaction
来开启事务,通过 commit
来关闭事务,如果 SQL 语句中没有这两个命令,默认情况下每一条 SQL 都是一个独立的事务,在执行完成后自动提交。
比如:
update user set name='重塑' where id=1;复制代码
假设我只执行这一条更新语句,在我关闭 MySQL 客户端然后重新打开一个新的客户端后,可以看到 user 表中的 name 字段值全变成了「重塑」,这也印证了这条更新语句在执行后已被自动提交。
自动提交是 MySQL 的一个默认属性,可以通过 SHOW VARIABLES LIKE 'autocommit'
语句来查看,当它的值为 ON
时,就代表开启事务的自动提交。
mysql> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+| Variable_name | Value | +---------------+-------+| autocommit | ON | +---------------+-------+1 row in set (0.00 sec)复制代码
我们可以通过 SET autocommit = OFF
来关闭事务的自动提交。
然而,即便我们已经将 autocommit
变量的值改为 OFF
关闭事务自动提交了,在执行某些 SQL 语句的时候,MySQL 还是会将事务自动提交掉,这被称为隐式提交。
会触发隐式提交的 SQL 语句有:
create
, drop
, alter
, truncate
create/drop user
, grant
, set password
时刻 | 事务A | 事务B |
---|---|---|
1 | begin; | |
2 | update user set name='重塑' where id=1; | |
3 | select name from user where id=1;(N1) | |
4 | begin; | |
5 | select name from user where id=1;(N2) |
In Transaktion B gibt es zwei Abfrageanweisungen N1 und N2. Die Ausführungsergebnisse sind N1=Hedgehog und N2=Reshape, was bewiesen werden kann.
Die Isolationsstufe einer Transaktion gibt die Sichtbarkeit von Änderungen an, die in einer Transaktion innerhalb und zwischen Transaktionen vorgenommen werden. Niedrigere Isolationsstufen ermöglichen in der Regel eine höhere Parallelität und einen geringeren Systemaufwand.
Im SQL-Standard sind vier Transaktionsisolationsstufen definiert: „Read Uncommitted“, „Read Committed“, „Repeatable Read“ und „Serializable“.
Um diese vier Isolationsstufen und ihre jeweiligen Phänomene im Detail zu erklären, gehen wir davon aus, dass zwei Transaktionen ausgeführt werden sollen. Der Ausführungsinhalt lautet wie folgt:
Zeit | Transaktion A | Transaktion B |
---|---|---|
1 set name='remodel' where id=1; 4 | Wählen Sie den Namen des Benutzers aus, bei dem die ID = 1 ist. (N1) | |
5 | |
7 |
8 | ||
3.1 Nicht festgeschrieben lesen | Unter der Isolationsstufe „Nicht festgeschrieben lesen“ sind Änderungen in einer Transaktion für andere Transaktionen sichtbar, auch wenn sie nicht festgeschrieben sind | . Wenn im obigen Szenario die Isolationsstufe der Datenbank nicht festgeschrieben gelesen wird, kann Transaktion A die geänderten Daten der nicht festgeschriebenen Transaktion B lesen, dh die Änderung von Transaktion B zum Zeitpunkt 3 ist für Transaktion A sichtbar, also N1 = Umformen, N2=umformen, N3=umformen. |
Unter der Isolationsstufe „Read Committed“ sind | Änderungen in einer Transaktion für andere Transaktionen erst sichtbar, nachdem sie festgeschrieben wurden | .
Wenn im obigen Szenario die Isolationsstufe der Datenbank festgeschrieben ist, ist die Änderung von Transaktion B zum Zeitpunkt 3 für Transaktion A und die Abfrage nicht sichtbar, da Transaktion A die Daten erst lesen kann, nachdem Transaktion B übermittelt wurde Bei N2 ist es nach der Übermittlung von Transaktion B für Transaktion A sichtbar. Also N1=Igel, N2=Neuerfindung, N3=Neuerfindung. |
Wiederholbares Lesen ist MySQLs Standard-Transaktionsisolationsstufe | . Unter der Isolationsstufe „Wiederholbares Lesen“ sind die Ergebnisse immer konsistent, wenn Sie denselben Datensatz in einer Transaktion mehrmals abfragen. | Wenn im obigen Szenario die Isolationsstufe der Datenbank wiederholbares Lesen ist, sind ihre Werte beide „Igel“, da sich die Abfragen N1 und N2 in einer Transaktion befinden und N3 eine Abfrage ist, die nach Transaktion A ausgeführt wird Die Änderungen an Transaktion B sind sichtbar, also N3 = Umgestaltung. | 3.4 Serialisierbar
Transaktionen seriell ausgeführt, und beim Schreiben wird eine Schreibsperre hinzugefügt. Daher treten keine Abweichungen auf. | Wenn im obigen Szenario die Isolationsstufe der Datenbank serialisierbar ist, wird Transaktion A zuerst geöffnet und blockiert, wenn Transaktion B geöffnet wird. Transaktion B wird erst geöffnet, wenn Transaktion A übermittelt wird, also N1=Hedgehog, N2= Igel . Die Abfrage bei N3 wird ausgeführt, nachdem Transaktion B übermittelt wurde (Transaktion B wird zuerst blockiert und die Ausführungsreihenfolge liegt vor der N3-Abfrageanweisung), also ist N3 = Umformung. | 4. Durch Isolationsstufen verursachte Probleme | Wenn Transaktionen gleichzeitig ausgeführt werden, treten viele Probleme auf, z. B. Dirty Read (Dirty Read), nicht wiederholbares Lesen (Non-Repeatable Read) und Phantom Read usw. Im Folgenden werden diese Sachverhalte anhand verschiedener Beispiele näher erläutert.
Sehen Sie sich den folgenden Fall an, vorausgesetzt, dass die Isolationsstufe nicht festgeschrieben ist:
ZeitTransaktion ATransaktion B
1begin;
begin; 3
update Benutzer set name = 'reshape' wob 5 mit;
Transaktion A | Transaktion B | |
---|---|---|
beginnen; ?? | 4 | Wählen Sie den Namen des Benutzers aus id= 1;(N1) |
5 |
||
6 | Name vom Benutzer auswählen, wobei id=1;(N2)||
7 | commit ; |
|
8 | select name from user where id=1;(N3)||
Unter der Isolationsstufe „Read Committed“ können Transaktionen Daten lesen, die von anderen Transaktionen übermittelt wurden. Im obigen Fall sind die Ergebnisse N1=Igel, N2=Umformung, N3=Umformung. In Transaktion A gibt es zwei identische Abfragen N1 und N2, aber die Ergebnisse dieser beiden Abfragen sind nicht identisch. Dies ist nicht lesbar wiederholt. |
Nicht wiederholbare Lesevorgänge erfolgen in den Isolationsstufen „Read Uncommitted“ und „Read Committed“. |
|
Phantom Read | (Phantom Read) bedeutet, dass, wenn eine Transaktion Datensätze in einem bestimmten Bereich liest, eine andere Transaktion einen neuen Datensatz in den Bereich einfügt | |
Zeit | Transaktion A
beginnen;
2.Name vom Benutzer auswählen (2, '五条人') ; (N3)8 | commit; | |||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Dirty Read | Non-Repeatable Read | Phantom Read | Konzept | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
√ | √ | √ | Die Änderungen in der Transaktion sind für andere Transaktionen sichtbar, auch wenn sie nicht committet sind | ||||||||||||
√ |
√ | Transaktion Die Änderungen in werden erst nach der Übermittlung für andere Transaktionen sichtbar | Serialisierbar | ||||||||||||
Transaktionen werden seriell ausgeführt, beim Lesen werden Lesesperren hinzugefügt und beim Schreiben werden Schreibsperren hinzugefügt |
In der obigen Diskussion wissen wir bereits, dass eine Transaktion eine | Leseansicht erstellt, wenn sie beginnt, und es gibt zwei Möglichkeiten, eine Transaktion zu starten, eine ist Transaktion beginnen/starten , die andere ist Transaktion mit konsistentem Snapshot starten , durch diese beiden Möglichkeiten, eine Transaktion zu starten, wird der Zeitpunkt der Erstellung einer Auch die Leseansicht unterscheidet sich:
|
||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Transaktion A | Transaktion B | ||||||||||||||
1 |
Transaktion mit konsistentem Snapshot starten; |
||||||||||||||
2 | Transaktion mit konsistentem Snapshot starten; | ||||||||||||||
Benutzer aktualisieren set name='reshape' where id = 1; Benutzer mit ID =1;(N2) |
|||||||||||||||
7 | commit;|||||||||||||||
Analysieren Sie dann den laufenden Prozess von MVCC basierend auf der oben beschriebenen Versionskette und der Leseansicht, wenn die beiden Transaktionen gestartet werden. Das obige Bild ist die Leseansicht, wenn zwei Transaktionen gestartet werden. Wenn die Aktualisierungsanweisung von Transaktion B ausgeführt wird, lautet die Versionskette der Zeilen-ID = 1 wie folgt. Sehen wir uns zunächst die Abfrageanweisung bei N1 an. Die aktuelle 于是循着Mein Verständnis von MySQL Teil 4: Transaktionen, Isolationsstufen und MVCC来到 再来看N2处的查询语句,此时事务B已提交,Mein Verständnis von MySQL Teil 4: Transaktionen, Isolationsstufen und MVCC还是如上图所示,由于当前版本的事务ID等于事务A读视图中的
5.4 RC 与 RR 生成读视图的时机对比上面所讨论的 MVCC 运行过程都是针对可重复读(RR, Repeatable Read)隔离级别的,如果是读已提交(RC, Read Committed)级别呢? 上文中已经讨论过读已提交隔离级别中关于不可重复读的情况了,这里就不再举例,直接给出结论就可以了。
对于上文中描述 MVCC 执行过程中的例子,如果隔离级别是读已提交(RC, Read Committed):
DB_TRX_ID=1 Transaktions-ID=1, die zufällig dem Transaktions-ID-Wert von Transaktion A entspricht, der die Version der Zeile ist, als Transaktion A wurde gestartet. Natürlich ist sie für Transaktion A sichtbar, daher wird der Name = 'Hedgehog' in Zeile id = 1 gelesen, dh der endgültige N1 = Hedgehog. Sehen Sie sich die Abfrageanweisung bei N2 an. Zu diesem Zeitpunkt wurde Transaktion B übermittelt und die Versionskette ist immer noch wie in der Abbildung oben dargestellt, da die Transaktions-ID der aktuellen Version mit der
|
5.6 Phantom-Lesevorgänge treten bei wiederholbaren Lesevorgängen auf Grund | Nachdem wir MVCC verstanden haben, schauen wir uns die Gründe an, warum Phantom-Lesevorgänge unter der Isolationsstufe für wiederholbare Lesevorgänge auftreten. Wie oben erwähnt, kommt es genau aufgrund des aktuellen Lesevorgangs zu Phantom-Lesevorgängen unter der Isolationsstufe wiederholbarer Lesevorgänge. Sehen wir uns zunächst das Beispiel an. ( 1) | |
---|---|---|
3 |
begin; |
|
4 |
in Benutzerwerte einfügen (2, '五条人'); |
|
5 | verpflichten; | |
Die Abfragen bei N1 und N2 müssen sehr deutlich gemacht haben, dass es sich bei allen um „Igel“ handelt. Die bei N3 verwendete Abfrageanweisung lautet 加锁自然是防止别人修改,那么理所当然,锁住的当然也就是记录的最新版本了。所以,在使用 for update zur Abfrage verwendet wird, wird current read verwendet, um die neueste Version des Zieldatensatzes zu lesen, sodass die Abfrageanweisung bei N3 Transaktion B-Datensätze einfügt für Transaktion A nicht sichtbar sind, werden ebenfalls abgefragt, und es kommt zu Phantomlesungen. Die aktuellen Leseanweisungen sind:
(Video) |
Das obige ist der detaillierte Inhalt vonMein Verständnis von MySQL Teil 4: Transaktionen, Isolationsstufen und MVCC. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!