So implementieren Sie Zeilensperren, Tabellensperren und Deadlocks im MySQL-Sperrmechanismus

1. Was ist eine MySQL-Sperre? Welche Arten von Schlössern gibt es?

Sperrdefinition:
Auf dieselbe Ressource kann nur ein Thread gleichzeitig zugreifen
In Die Datenbank außer Neben dem Wettbewerb um herkömmliche Computerressourcen (wie CPU, E/A usw.) sind Daten auch eine Ressource, die von vielen Benutzern gemeinsam genutzt wird. Die Gewährleistung der Konsistenz und Wirksamkeit des gleichzeitigen Zugriffs auf Daten ist ein Problem, das alle Datenbanken lösen müssen. Sperrkonflikte sind ebenfalls ein wichtiger Faktor, der sich auf die Leistung des gleichzeitigen Zugriffs auf Datenbanken auswirkt.

Was das optimistische Sperren am häufigsten verwendet, ist der Versionsdatensatz, der die Version widerspiegelt, bei der es sich eigentlich um ein Logo handelt.

Zum Beispiel: update test set a=a-1 where id=100 and a> 0; Die entsprechende Version ist das Feld a. Es ist nicht unbedingt erforderlich, dass dieses Feld vorhanden ist und nur dann ausgelöst wird diese Bedingung ist erfüllt Datenoperationen (Lesen oder Schreiben)

Lesesperre (gemeinsame Sperre): Für dieselben Daten können mehrere Lesevorgänge gleichzeitig ausgeführt werden, ohne sich gegenseitig zu beeinflussen. Schreibsperre (exklusive Sperre): Bevor der aktuelle Schreibvorgang abgeschlossen ist, werden andere Schreibsperren und Lesesperren blockiert.

Aus der Granularität von Datenoperationen
Sperre auf Tabellenebene: Die Sperre auf Tabellenebene ist die Sperre mit der größten Granularität in MySQL und gibt die aktuelle Operationssperre an die gesamte Tabelle (MyISAM-Engine verwendet standardmäßig Sperren auf Tabellenebene und unterstützt nur Sperren auf Tabellenebene). Wenn Sie beispielsweise ein Datenelement in einer 100.000-Tabelle aktualisieren, werden andere Transaktionen ausgeschlossen, bevor diese Aktualisierung die Transaktion festschreibt, und die Granularität ist sehr groß.
Sperre auf Zeilenebene: Die Sperre auf Zeilenebene ist die feinkörnige Sperre in MySQL, was bedeutet, dass nur die aktuell bearbeitete Zeile gesperrt ist (
Es wird basierend auf dem Index implementiert. Sobald also ein bestimmter Sperrvorgang keinen Index verwendet, degeneriert die Sperre in eine Tabellensperre

)

Sperre auf Seitenebene: Sperre auf Seitenebene ist eine Sperrgranularität in MySQL zwischen Zeilenebene Sperren und Sperren auf Tabellenebene. Sperren einer Gruppe benachbarter Datensätze gleichzeitig Art des Gedankenspessimistische Sperre: Seien Sie konservativ (pessimistisch), was Daten betrifft, die von der Außenwelt geändert werden (einschließlich anderer aktueller Transaktionen des Systems und der Transaktionsverarbeitung von externen Systemen). den gesamten Datenverarbeitungsprozess.
Optimistische Sperre: Bei der optimistischen Sperre wird davon ausgegangen, dass die Daten unter normalen Umständen keine Konflikte verursachen. Wenn die Daten also zur Aktualisierung übermittelt werden, wird der Konflikt der Daten offiziell erkannt. Wenn ein Konflikt festgestellt wird, wird ein Fehler angezeigt zurückgegeben. Informationen und dann das Geschäft erneut versuchen Datensätze, deren Schlüsselwerte innerhalb des Bedingungsbereichs liegen, aber nicht vorhanden sind, werden als „Lücken“ bezeichnet. Der Zweck der Lücken besteht darin, Phantomlesungen zu verhindern Absichtssperre : Absichtssperrpunkte Für die Absichts-Gemeinschaftssperre (IS) und die Absichts-Exklusivsperre (IX) besteht der Zweck der Absichtssperre darin, anzuzeigen, dass eine Transaktion eine Zeile in einer Tabelle sperrt oder sperren wird
2. Zeile Unterschied zwischen Sperre und Tabellensperre
Sperre auf Tabellenebene ist die Sperre mit der größten Sperrgranularität in MySQL

, was bedeutet, dass die gesamte Tabelle des aktuellen Vorgangs gesperrt wird umzusetzen. Die am häufigsten verwendeten MYISAM und INNODB unterstützen das Sperren auf Tabellenebene.

Merkmale: Geringer Overhead, schnelles Sperren ; keine große Sperrgranularität, höchste Wahrscheinlichkeit von Sperrkonflikten und geringste Parallelität.

Sperre auf Zeilenebene ist die detaillierteste Sperre in MySQL

, was bedeutet, dass nur die Zeile des aktuellen Vorgangs gesperrt ist. Sperren auf Zeilenebene können Konflikte bei Datenbankvorgängen erheblich reduzieren. Seine Sperrgranularität ist am kleinsten, aber der Sperraufwand ist auch am größten. Eigenschaften: Hoher Overhead, langsame Sperren; die Sperrgranularität ist am geringsten, die Wahrscheinlichkeit von Sperrkonflikten ist am höchsten. Verwendung: InnoDB-Zeilensperren werden vergeben Indexeinträge im Index Um dies durch Sperren zu erreichen, verwendet InnoDB nur Sperren auf Zeilenebene, wenn
Daten über Indexbedingungen abruft
Andernfalls verwendet InnoDB Tabellensperren

In Die folgende Aktualisierungsanweisung lautet b: Wenn das allgemeine Feld keine Indexspalte ist, wird die Sperre auf Zeilenebene in eine Sperre auf Tabellenebene geändert.

update from test set a=100 where b='100';

Nehmen wir nun ein praktisches Beispiel, um zu sehen, wie innnodb Zeilensperren verwendet. Daten in der aktuellen Tabelle:

Öffnen Sie zuerst zwei Sitzungsfenster und stellen Sie dann die MySQL-Transaktionsebene auf „Nicht senden“ ein Ebene: Fenster Sitzung eins:

Fenster Sitzung zwei:

 其中会话2的update一直都在Running中，一直到超时结束，或者会话1提交事务后才会Running结束。

可以通过show VARIABLES like "%innodb_lock_wait_timeout%" 查询当前mysql设置的锁超时时间,默认是50秒。 

可以通过set innodb_lock_wait_timeout = 60; 设置锁的超时时间。

只有在第一个会话提交后，第二个会话的更新语句才能成功执行。这代表了innodb用了锁。

那怎么确定是用了行锁呢？

 

 总结:会话一更新id=125的时候，给这条数据add lock了，那么在会话2中再次更新id=125的时候，这条数据是locked中的。这个lock加的是id=125这条记录。证明默认情况下id=125这条记录会加上行锁，除了这条记录之外的其它记录都可以成功地操作。

三、InnoDB死锁概念和死锁案例

发生死锁是因为多个事务相互持有和请求锁，并形成了一个循环依赖关系。多个事务同时锁定同一个资源时，也会产生死锁。在一个事务系统中，死锁是确切存在并且是不能完全避免的。

自动检测事务死锁并回滚一个事务，同时返回错误信息的功能由InnoDB自动实现。它根据某种机制来选择那个最简单（代价最小）的事务来进行回滚

死锁场景一之select for update:

产生场景：两个transaction都有两个select for update，transaction a先锁记录1，再锁记录2；而transaction b先锁记录2，再锁记录1

写锁：for update，读锁：for my share mode show engine innodb status

验证下死锁的场景:

 第一步更新会话一:

start TRANSACTION;
select * from wnn_test where a=199 for update;

第二步更新会话二:

start TRANSACTION;
select * from wnn_test where a=101 for update;

第三步更新会话一:

select * from wnn_test where a=101 for update;

第四步更新会话二;

select * from wnn_test where a=199 for update;

在更新到第三步和第四步的时候，已经发生了死锁。

来看下执行的日志:

show engine innodb status;最后一个锁的时间，锁的表，引起锁的语句。其中session1被锁 14秒(ACTIVE 14)，session 2被锁了10秒(Active 10)

死锁场景二之两个update

产生场景：两个transaction都有两个update，transaction a先更新记录1，再更新记录2；而transaction b先更新记录2，再更新记录1

 产生日志：

 注意：仔细查看上面2个例子可以发现一个现象，当2条资源锁住后，再执行第三个会执行成功，但是第四个会提示死锁。在mysql5.7中，执行第三个的时候就会一直在Running状态了，本博文使用的是mysql8.0 ，其中 有这个参数 innodb_deadlock_detect 可以用于控制 InnoDB 是否执行死锁检测，当启用了死锁检测时（默认设置），InnoDB 自动执行事务的死锁检测，并且回滚一个或多个事务以解决死锁。InnoDB 尝试回滚更小的事务，事务的大小由它所插入、更新或者删除的数据行数决定。

 那么这个innodb_deadlock_detect参数，到底要不要启用呢？

对于高并发的系统，当大量线程等待同一个锁时，死锁检测可能会导致性能的下降。此时，如果禁用死锁检测，而改为依靠参数 innodb_lock_wait_timeout 执行发生死锁时的事务回滚可能会更加高效。
通常来说，应该启用死锁检测，并且在应用程序中尽量避免产生死锁，同时对死锁进行相应的处理，例如重新开始事务。

Nur wenn Sie sicher sind, dass die Deadlock-Erkennung die Leistung des Systems beeinträchtigt und das Deaktivieren der Deadlock-Erkennung keine negativen Auswirkungen hat, können Sie versuchen, die Option innodb_deadlock_detect zu deaktivieren. Darüber hinaus Wenn die InnoDB-Deadlock-Erkennung deaktiviert ist, muss der Wert des Parameters innodb_lock_wait_timeout angepasst werden, um den tatsächlichen Anforderungen zu entsprechen.

4. So vermeiden Sie Deadlocks während der Programmentwicklung

Das Wesen von Sperren besteht darin, dass Ressourcen miteinander konkurrieren und aufeinander warten. Oft ist die Reihenfolge von zwei (oder mehr) Sitzungssperren inkonsistent

So effektiv Vermeiden Sie Folgendes:

Wenn Sie im Programm mehrere Tabellen bearbeiten, versuchen Sie, in derselben Reihenfolge auf diese zuzugreifen (um die Bildung einer Warteschleife zu vermeiden).

Wenn Sie einzelne Tabellendaten in Stapeln verarbeiten, sortieren Sie die Daten zuerst (bis). Vermeiden Sie die Bildung einer Warteschleife) Warteschleife) A Thread-ID: 1, 10, 20 wird in der Reihenfolge gesperrt B Thread-ID: 20, 10, 1 In diesem Fall ist es einfach zu sperren.

Wenn möglich, wandeln Sie große Transaktionen in kleine Transaktionen um oder öffnen Sie die Transaktion überhaupt nicht. Wählen Sie für Update==>insert==>update = in Update auf doppeltem Schlüssel einfügen

Um Tabellensperren zu vermeiden Es wird empfohlen, so weit wie möglich Indizes zu verwenden, um auf Daten zuzugreifen und Vorgänge ohne Where-Bedingungen zu vermeiden, da mithilfe von Indizes Zeilensperren aufgezeichnet werden können, ohne Tabellensperren zu verursachen.

Verwenden Sie äquivalente Abfragen anstelle von Bereichsabfragen, um Daten abzufragen, Datensätze zu treffen usw Vermeiden Sie Lückensperren bei Parallelität. Betrifft 1, 10, 20 und andere Werte, bei denen die ID im (1,10,20)-Bereich liegt. Abfrage-ID>1 und ID

Das obige ist der detaillierte Inhalt vonSo implementieren Sie Zeilensperren, Tabellensperren und Deadlocks im MySQL-Sperrmechanismus. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!