Transaktionen und Sperren in MySQL

In diesem Artikel werden die relevanten Kenntnisse zu Datenbanktransaktionen und -sperren ausführlich vorgestellt. Vor allem einige konzeptionelle Dinge mögen langweilig erscheinen, aber als qualifizierter Programmierer sollte und muss man sie beherrschen. Dieses theoretische Wissen ist wie die innere Stärke einer Person. Wenn wir normalerweise programmieren, ist es äußere Stärke. Nur indem wir sowohl innere als auch äußere Fähigkeiten üben und uns gegenseitig fördern, können wir das Niveau eines Kampfkunstmeisters erreichen. Okay, ohne weitere Umschweife, fangen wir an.

Datenbanktransaktion

Grenze der Transaktion

Startgrenze der Transaktion (Begin)
Endgrenze der Transaktion (Commit): Senden Sie die Transaktion und speichern Sie sie anschließend dauerhaft wird durch den Status der Transaktionsdatenbank aktualisiert.
Abnormale Endgrenze der Transaktion (Rollback): Brechen Sie die Transaktion ab und bringen Sie die Datenbank in den Ausgangszustand zurück, bevor die Transaktion ausgeführt wird.

Jedes Mal, wenn Sie ein MySQL.exe-Programm starten, erhalten Sie eine separate Datenbankverbindung. Jede Datenbankverbindung verfügt über eine globale Variable „Autocommit“, die den aktuellen Transaktionsmodus darstellt. Sie verfügt über zwei optionale Werte:

• 0: stellt den manuellen Commit-Modus dar

• 1: Zeigt den automatischen Übermittlungsmodus an. Der Standardwert ist

. Wir können diesen Wert anzeigen und ändern.

Vier Merkmale von Datenbanktransaktionen (ACID):

• Atomizität: Eine Transaktion ist eine atomare Operationseinheit und ihre Datenänderung. Entweder wird alles ausgeführt oder gar nicht ausführen;

• Konsistent: Die Daten müssen konsistent bleiben, wenn die Transaktion gestartet und abgeschlossen wird

• Isolation: Die Datenbank Das System bietet einen bestimmten Isolationsmechanismus, um sicherzustellen, dass Transaktionen in einer „unabhängigen“ Umgebung ausgeführt werden, die nicht durch externe gleichzeitige Vorgänge beeinträchtigt wird.

• Dauerhaft: Nachdem die Transaktion abgeschlossen ist, wird sie geändert Die Daten sind dauerhaft und können auch bei einem Systemausfall beibehalten werden.

Transaktionsisolationsstufe

Bei der Datenbanktransaktionsisolationsstufe geht es nur darum, ob eine Transaktion die Zwischenergebnisse anderer Transaktionen lesen kann.

• Nicht festgeschriebene Inhalte lesen (nicht festgeschriebene Inhalte lesen)
  Auf dieser Isolationsstufe können alle Transaktionen das Ergebnis der Ausführung anderer nicht festgeschriebener Transaktionen sehen. Diese Isolationsstufe wird in praktischen Anwendungen selten verwendet, da ihre Leistung nicht viel besser ist als die anderer Stufen. Das Lesen nicht festgeschriebener Daten wird auch Dirty Read genannt.

• Read Committed
  Dies ist die Standardisolationsstufe für die meisten Datenbanksysteme (aber nicht die MySQL-Standardeinstellung). Es erfüllt die einfache Definition von Isolation: Eine Transaktion kann nur Änderungen sehen, die von festgeschriebenen Transaktionen vorgenommen wurden. Diese Isolationsstufe unterstützt auch das sogenannte nicht wiederholbare Lesen, da andere Instanzen derselben Transaktion während der Verarbeitung dieser Instanz möglicherweise neue Commits haben, sodass dieselbe Auswahl möglicherweise unterschiedliche Ergebnisse zurückgibt.

• Wiederholbares Lesen (wiederlesbar)
  Dies ist die standardmäßige Transaktionsisolationsstufe von MySQL, die sicherstellt, dass mehrere Instanzen derselben Transaktion beim gleichzeitigen Lesen von Daten dieselben Datenzeilen sehen. Theoretisch führt dies jedoch zu einem weiteren heiklen Problem: Phantom Read (Phantom Read). Einfach ausgedrückt bedeutet Phantom-Lesen, dass eine andere Transaktion eine neue Zeile in den Bereich einfügt, wenn der Benutzer die Datenzeilen im Bereich erneut liest " OK. InnoDB- und Falcon-Speicher-Engines lösen dieses Problem durch den Multiversion Concurrency Control (MVCC)-Mechanismus.

• Serialisierbar (serialisierbar)
  Dies ist die höchste Isolationsstufe. Sie löst das Phantomleseproblem, indem Transaktionen so angeordnet werden, dass sie nicht miteinander in Konflikt geraten können. Kurz gesagt, es fügt jeder gelesenen Datenzeile eine gemeinsame Sperre hinzu. Auf dieser Ebene kann es zu zahlreichen Zeitüberschreitungen und Sperrenkonflikten kommen.

Je höher die Isolationsstufe, desto vollständiger und konsistenter können die Daten garantiert werden, aber desto größer ist auch die Auswirkung auf die Parallelitätsleistung.
Für die meisten Anwendungen können Sie effektiv in Betracht ziehen, die Isolationsstufe des Datenbanksystems auf „Read Committed“ zu setzen, wodurch schmutzige Lesevorgänge vermieden werden können und eine bessere Parallelitätsleistung erzielt wird. Obwohl dies zu Parallelitätsproblemen wie nicht wiederholbaren Lesevorgängen, verschwendeten und verlorenen Aktualisierungen zweiter Art führt, können solche Probleme in einzelnen Fällen, in denen solche Probleme auftreten, von der Anwendung mithilfe pessimistischer Sperren und optimistischer Sperren gesteuert werden.

Weitergabe von Transaktionen

1. PROPAGATION_REQUIRED
   Fügen Sie die aktuell ausgeführte Transaktion hinzu. Wenn die aktuelle Transaktion nicht existiert, starten Sie eine neue Transaktion. Das Standardverhalten der Transaktionsweitergabe der Spring-Betriebsdatenbank ist propagation_required.

2. PROPAGATION_SUPPORTS
   Wenn es sich derzeit in einer Transaktion befindet, wird es als Transaktion ausgeführt. Wenn es sich nicht mehr in einer Transaktion befindet, wird es als Nicht-Transaktion ausgeführt.

3. PROPAGATION_MANDATORY
   Muss innerhalb einer Transaktion ausgeführt werden. Mit anderen Worten: Er kann nur von einer übergeordneten Transaktion aufgerufen werden. Andernfalls wird er eine Ausnahme auslösen.

4. PROPAGATION_REQUIRES_NEW
   Unterbrechen Sie die aktuelle Transaktion und starten Sie eine neue Transaktion.

5. PROPAGATION_NOT_SUPPORTED

   Transaktionen werden derzeit nicht unterstützt. Wenn es sich um eine Transaktion handelt, wird die aktuelle Transaktion ausgesetzt und im nicht-transaktionalen Verhalten ausgeführt.

6. PROPAGATION_NEVER
   kann nicht innerhalb einer Transaktion ausgeführt werden und es wird eine Ausnahme ausgelöst, wenn es innerhalb einer Transaktion ausgeführt wird.

7. PROPAGATION_NESTED
   Die verschachtelte Transaktion hängt von der übergeordneten Transaktion ab. Wenn die übergeordnete Transaktion festgeschrieben wird, wird sie zurückgesetzt.

Sperre auf Zeilenebene

Drei Arten von Sperren in MySQL:

Zeilenebene: Engine INNODB, Sperrung einzelner Zeilendatensätze
Seite Ebene: Engine BDB sperrt jeweils eine benachbarte Gruppe von Datensätzen.
Tabellenebene: Engine MyISAM, verstanden als Sperren der gesamten Tabelle, die gleichzeitig gelesen, aber nicht geschrieben werden kann.
Die Eigenschaften der drei Arten von Sperren lassen sich grob wie folgt zusammenfassen:
1) Sperren auf Tabellenebene: geringer Overhead, keine Deadlocks; die höchste Wahrscheinlichkeit von Sperrenkonflikten geringste Parallelität.
2) Sperren auf Zeilenebene: Es kann zu hohem Overhead und langsamen Sperren kommen. Die Sperrgranularität ist am geringsten und die Parallelität ist am höchsten.
3) Seitensperre: Die Kosten und die Sperrzeit liegen zwischen Tabellensperren und Zeilensperren. Die Sperrgranularität liegt zwischen Tabellensperren und Zeilensperren, und die Parallelität ist durchschnittlich.

Wovon wir hier hauptsächlich sprechen, sind Sperren auf Zeilenebene. Im Flash-Sale-System verwenden wir im Allgemeinen Sperren auf Zeilenebene für den Produktbestand, da der Lagerbestand während des Flash-Sales ein sehr wichtiges Datum ist. Wir erstellen eine Datenbanktabelle. Die folgenden Einstellungen können angezeigt werden:

ENGINE = InnoDB AUTO_INCREMENT=10 DEFAULT CHARACTER SET = utf8 comment='用户表

Setzen Sie die Engine auf InnoDB. InnnoDB unterscheidet sich von anderen Engines: Erstens unterstützt es Transaktionen (TRANCSACTION) und zweitens verwendet es Zeilen-. Niveauschlösser.

InnoDB中两种模式的行级锁：

1）共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 
( Select * from table_name where ……lock in share mode) 
2）排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和 排他写锁。(select * from table_name where…..for update)

为了允许行锁和表锁共存，实现多粒度锁机制；同时还有两种内部使用的意向锁（都是表锁），分别为意向共享锁和意向排他锁。

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

注意：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

行级锁的优缺点

行级锁定的优点：

• 当在许多线程中访问不同的行时只存在少量锁定冲突。

• 回滚时只有少量的更改。

• 可以长时间锁定单一的行。

行级锁定的缺点：

• 比页级或表级锁定占用更多的内存。

• 当在表的大部分数据上使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。如果你在大部分数据上经常进行GROUP BY操作或

者必须经常扫描整个表，比其它锁定明显慢很多。

hibernate中通过行级锁实现的悲观锁。

一些例子：

假设有个表单products ，里面有id跟name二个栏位，id是主键。 
1: 明确指定主键，并且有此条记录，执行row lock。若查无此记录，无lock。

SELECT * FROM products WHERE id='3' FOR UPDATE;SELECT * FROM products WHERE id='3' and name="cat" FOR UPDATE;

2: 无主键，执行table lock。

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

3: 主键不明确，table lock。

SELECT * FROM products WHERE id<>&#39;3&#39; FOR UPDATE;

注意： FOR UPDATE仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。此外，如果A与B都对表id进行查询但查询不到记录，则A与B在查询上不会进行row锁，但A与B都会获取排它锁，此时A再插入一条记录的话则会因为B已经有锁而处于等待中，此时B再插入一条同样的数据则会抛出Deadlock found when trying to get lock; try restarting transaction。然后释放锁，此时A就获得了锁而插入成功。

以上就是MySQL中的事务与锁的内容，更多相关内容请关注PHP中文网（www.php.cn）！