Detaillierte Erklärung des MySQL-Sperrmechanismus

Ich habe in dieser Zeit die MySQL-Datenbank erlernt. Das Projektteam hat Oracle verwendet, daher ist das Verständnis von MySQL nicht tiefgreifend. Dieser Artikel ist hauptsächlich eine Zusammenfassung von MySQL-Sperren.

MySQL Sperren sind hauptsächlich in 3 Kategorien unterteilt:

Sperre auf Tabellenebene: Die Speicher-Engine ist Myisam. Das Sperren der gesamten Tabelle zeichnet sich durch geringen Overhead, schnelles Sperren, starkes Sperren, die höchste Wahrscheinlichkeit von Sperrkonflikten und die geringste Parallelität aus.

Sperre auf Seitenebene: Die Speicher-Engine ist BDB. Sperren Sie eine bestimmte Datenseite (16 KB oder so), Funktionen: Overhead und Sperrzeit liegen zwischen Tabellenebene und Zeilenebene; es kommt zu einem Deadlock, die Sperrstärke liegt zwischen Tabellenebene und zwischen Sperren und Zeilensperren ist die Parallelität durchschnittlich.

Sperre auf Zeilenebene: Die Speicher-Engine ist innodb. Das Sperren einer bestimmten Datenzeile weist folgende Merkmale auf: Die Sperrimplementierung ist komplizierter, teurer und die Sperrgeschwindigkeit ist langsam.

Basierend auf den oben genannten Merkmalen, nur aus Sperrsicht: Sperren auf Tabellenebene eignen sich besser für Anwendungen, die hauptsächlich abfragebasiert sind und nur eine kleine Datenmenge entsprechend aktualisiert haben Indexbedingungen, wie z. B. Web--Anwendungen; Sperren auf Zeilenebene eignen sich hingegen eher für Anwendungen, die eine große Anzahl gleichzeitiger Aktualisierungen einer kleinen Menge unterschiedlicher Daten basierend auf Indexbedingungen und gleichzeitigen Abfragen haben, wie z. B. einige Online-Transaktionsverarbeitungssystem (OLTP).

Als nächstes geben wir eine detaillierte Erläuterung der Sperren auf Zeilenebene. Sperren auf Zeilenebene sind hauptsächlich in die folgenden 7 Kategorien unterteilt: gemeinsam genutzt /Exklusive Sperre, Absichtssperre, Aufnahmesperre, Lückensperre, temporäre Sperre, Einfügungsabsichtssperre, automatische Inkrementsperre.

Gemeinsame Sperre/Exklusive Sperre:

Gemeinsame Sperre: Auch Lesesperre genannt, sie ermöglicht das Lesen, aber nicht das Schreiben. Gemeinsame Sperren können mit gemeinsamen Sperren verwendet werden. Anweisung:

select ... lock in share mode

Exklusive Sperre: Auch als Schreibsperre bekannt, weder Lesen noch Schreiben ist zulässig. Die exklusive Sperre kann nicht mit anderen Sperren verwendet werden. Anweisung:

select ... for update

in MySQL, Update, Löschen, insert, alter Diese Schreibvorgänge fügen standardmäßig exklusive Sperren hinzu. Auswählen fügt standardmäßig keinen Sperrtyp hinzu. Sobald die Aufgabe des Schreibens von Daten nicht abgeschlossen ist, können die Daten nicht von anderen Aufgaben gelesen werden, was größere Auswirkungen auf gleichzeitige Vorgänge hat.

Absichtssperre: innoDB Um Sperren mit mehreren Granularitäten zu unterstützen, dh um die Koexistenz von Sperren auf Zeilenebene und Sperren auf Tabellenebene zu ermöglichen, werden Absichtssperren verwendet werden vorgestellt. Absichtssperren bedeuten, dass Transaktionen irgendwann in der Zukunft möglicherweise gemeinsame / exklusive Sperren hinzufügen müssen, also erklären Sie eine Absicht im Voraus. Wenn jemand versucht, die gesamte Tabelle zu ändern, muss auf diese Weise nicht festgestellt werden, ob die Daten in der Tabelle gesperrt sind. Er muss nur darauf warten, dass die beabsichtigte Mutex-Sperre aufgehoben wird.

Intention shared lock (IS): Wenn eine Transaktion eine gemeinsame Sperre für bestimmte Datensätze in der Tabelle erhalten möchte, muss sie dies tun Erhalten Sie zunächst eine gemeinsame Sperre für die Tabelle.

Absichtliche Mutex-Sperre (IX): Wenn eine Transaktion eine Mutex-Sperre für bestimmte Datensätze in der Tabelle erhalten möchte, muss sie dies tun Fügen Sie zunächst eine Absichts-Mutex-Sperre hinzu.

意向锁其实不会阻塞全表扫描之外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

  记录锁（RS）：单个行记录上的锁。记录锁总是会锁住索引记录，如果innoDB存储引擎表

在建立的时候没有设置任何一个索引，那么innoDB存储引擎会使用隐式的主键来进行锁定。

间隙锁（GR）：间隙锁锁住记录中的间隔，即范围查询的记录。

 Select * From user where id between 1 and 10 for update

这个脚本会锁住1到10 的数据，以防止其他事务修改该区间的记录；

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效

临建锁（next-key Locks）：临建锁是记录锁和间隙锁的组合，锁的范围既包含记录又包含索引区间。默认情况下，innoDB使用临建锁来锁定记录。但当查询的索引含有唯一属性的时候，临建锁会进行优化，将其降级为记录锁，即仅锁住索引本身，不是范围。

临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

插入意向锁(insert intention locks):对已有数据行的修改和删除，必须加互斥锁，对于数据的插入，加插入意向锁。是专门针对于insert操作的。

自增锁（auto-inc locks）：是一种特殊的表级别的锁，专门针对事务插入auto-increment类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

---------------------------------------------------------分界线--------------------------------------------------------------

接下看讲一下其他的锁：

死锁：产生是因为线程锁之间交替等待产生的。值两个或两个以上的事务在执行过程中，因争夺资源而造成的一种相互等待的现象。

Mysql处理死锁的方法：根据数据写的数据量的大小来回滚小事务。

乐观/悲观锁：

乐观锁：乐观的假定大概率不会发生并发更新冲突，访问，处理数据的过程中不加锁，只在更新数据时根据版本号或时间戳判断是否有冲突，有则处理，无责提交事务。

如果系统并发量非常大，悲观锁会带来非常大的性能问题，选择使用乐观锁，现在大部分应用属于乐观锁

悲观锁:悲观的假定大概率会发生并发更新冲突，访问，处理数据前就加排他锁，在整个数据处理过程中锁定数据，事务提交或回滚后才释放锁。

优点：

悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。

缺点：

（a）在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会；

(b) Bei der schreibgeschützten Transaktionsverarbeitung gibt es keine Notwendigkeit, Sperren zu verwenden. Dadurch wird auch die Parallelität verringert Eine Transaktion sperrt eine Datenzeile. Andere Transaktionen müssen auf die Verarbeitung der Transaktion warten, bevor sie diese Anzahl von Zeilen verarbeiten.

Vorschlag:

1. Kontrollieren Sie die Größe der Transaktion (Operation Menge der geschriebenen Daten)
2. Versuchen Sie bei der Verwendung von Sperren, diese in Verbindung mit Feldern zu verwenden, die Indizes enthalten, um ein Upgrade auf Tabellensperren zu vermeiden.
3. Bereichsabfragen und versuchen Sie, die Größe zu reduzieren von Transaktionen basierend auf Bereichsabfragen
4. Wenn das Unternehmen Sperren verwenden muss und der Sperrkonflikt besonders hoch ist, wechseln Sie zu Tabellensperren
5. Sie können die Transaktion entsprechend den projekteigenen Bedingungen anpassen innodb_flush_log_at_trx_commit

Empfohlenes Lernen: MySQL-Tutorial

Das obige ist der detaillierte Inhalt vonDetaillierte Erklärung des MySQL-Sperrmechanismus. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!