Überarbeitung des MySQL-Sperrmechanismus

Artikelverzeichnis

• Sperre
• • Sperrklassifizierung
  • Gemeinsame Sperre (Lesesperre, gegenseitiger Lese- und Schreibausschluss, Lesen und Lesen beeinflussen sich nicht gegenseitig)
  • Exklusive Sperre (Schreibsperre, exklusiv Sperre)
  • Der Unterschied zwischen gemeinsam genutzten Sperren und exklusiven Sperren
• Tabellensperren
• • Absichtssperren: Absichts-Gemeinschaftssperren und Absichts-Exklusivsperren
  • • Warum müssen Sie Absichtssperren hinzufügen
  • Einzeltabelle Sperre
  • globale Tabellensperre
• Zeilensperre
• Zeilensperre wird zur Tabellensperre aktualisiert (Zeilensperre sperrt tatsächlich den Index. Wenn der Index nicht verwendet wird und die gesamte Tabelle gescannt wird, wird die gesamte Tabelle gesperrt)
• Ändern oder löschen Sie einen bestimmten Zeilendatensatz und die Zeilensperre ist nicht gesperrt. Beim Festschreiben einer Transaktion wird die Zeile immer durch eine Zeilensperre gesperrt.
• Datensatzsperre. Lückensperren. Die Rolle von Lückensperren: Phantom-Lesevorgänge verhindern
• Die Bedingungen für die automatische Verwendung von Lückensperren innodb
• Bereich der Lückensperren
  • Next-Key-Sperre [Sperrtaste]
  • Der Unterschied zwischen Datensatzsperre, Lückensperre und Zwischensperre
• Verwandte kostenlose Lernempfehlungen:

MySQL-Video-Tutorial

LockLock ist ein Mechanismus für Computer, um den Zugriff mehrerer Threads auf dieselbe gemeinsam genutzte Ressource zu koordinieren. Er wird hauptsächlich verwendet, um das Problem der Parallelitätssicherheit beim Zugriff mehrerer Threads zu lösen die gleiche gemeinsame Ressource.

Sperrklassifizierung

(1) Aus Sicht der Leistung ist es unterteilt in: optimistische Sperre und pessimistische Sperre

MySQL verwendet die Versionsnummernkontrolle und JVM verwendet CAS

(2) Vom Datenbankoperationstyp Es ist unterteilt in: Lesesperre (gemeinsame Sperre), Schreibsperre (exklusive Sperre) [Lesesperre und Schreibsperre sind beide pessimistische Sperren]

Lesesperre (gemeinsame Sperre): Für denselben Zeilendatensatz können mehrere Lesevorgänge ausgeführt werden gleichzeitig ausgeführt werden, es kann jedoch keine Transaktion ausgeführt werden

Schreibsperre (exklusive Sperre): Eine Transaktion, die eine exklusive Sperre erhält, kann Daten sowohl lesen als auch ändern. Bis die Transaktion, die die Schreibsperre erhält, abgeschlossen ist, werden andere Transaktionen daran gehindert, Schreib- oder Lesesperren zu erwerben.

(3) Aufgrund der Granularität ist es in Tabellensperren und Zeilensperren unterteilt.
• Hinweis:

(1) Lesesperren und Schreibsperren sind Sperren auf Zeilenebene, dh Transaktion 1 erhält eine Schreibsperre für Produkt A und der Erwerb von Schreibsperren für Produkt B durch Transaktion 2 blockieren sich nicht gegenseitig.

(2) Wenn die SQL-Anweisung Zeilensperren verwendet und SQL keine Indizes, sondern vollständige Tabellenscans verwendet, werden Sperren auf Zeilenebene zu Tabellensperren.

(3) Die Sperre wird nur aufgehoben, wenn ein Commit oder Rollback ausgeführt wird, und alle Sperren werden gleichzeitig aufgehoben. (4) Wenn die Innodb-Engine normale SQL-Anweisungen zum Ändern und Löschen ausführt, fügt sie den geänderten und gelöschten Zeilen Zeilensperren hinzu.

Gemeinsame Sperre (Lesesperre, gegenseitiger Lese- und Schreibausschluss, Lesen und Lesen beeinflussen sich nicht gegenseitig)

Wenn Transaktion A die gemeinsame Sperre verwendet, um bestimmte (oder einige) Datensätze abzurufen, kann Transaktion B diese Datensätze lesen und kann fortfahren. Fügen Sie eine gemeinsame Sperre hinzu, können Sie diese Datensätze jedoch nicht ändern (wenn Transaktion C diese Daten ändert oder löscht, wechselt sie in einen Sperrzustand, bis die Wartezeit für die Sperre abläuft oder Transaktion A festgeschrieben wird).

So verwenden Sie gemeinsame Sperren und Freigaben Gemeinsame Sperren

# 加锁SELECT ... LOCK IN SHARE MODE# 释放锁commit;rollback;

Funktion
• SELECT … LOCK IN SHARE MODE fügt mehreren Zeilen gelesener Datensätze eine gemeinsame Sperre (Share Lock) hinzu. Andere Transaktionen können diese Zeilen nur abfragen, diese Datensätze jedoch nicht ändern. Mehrere Transaktionen können dem gleichen Zeilendatensatz gemeinsame Sperren hinzufügen, sodass die Transaktion, die die gemeinsame Sperre erhält, möglicherweise nicht unbedingt in der Lage ist, die Zeilendaten zu ändern

• Verwendungsszenario:
  Lesen Sie die neueste Version des Ergebnissatzes und verhindern Sie gleichzeitig andere Transaktionen aus der Aktualisierung des Ergebnissatzes

  Zum Beispiel: Gleichzeitiger Vorgang am Produktinventar

• Exklusive Sperre (Schreibsperre, exklusive Sperre)
  

Auswählen... für Aktualisierung fügt eine exklusive Sperre zum Lesen des Zeilendatensatzes hinzu, und zwar nur Ermöglicht den Erhalt des Exklusiven. Die Sperrtransaktion ändert den Zeilendatensatz und verhindert, dass andere Transaktionen die Zeile ändern, genau wie eine Zeilensperre hinzugefügt wird, wenn eine normale Aktualisierungsanweisung ausgeführt wird.

So verwenden Sie exklusive Sperren

# 加排他锁select ... for update //排他锁 # 释放锁commit;rollback;

Der Unterschied zwischen gemeinsamen Sperren und exklusiven Sperren

• (1) Sobald eine Transaktion eine exklusive Sperre erhält, können andere Transaktionen keine exklusive Sperre mehr erwerben.

Mehrere Transaktionen können gemeinsame Sperren zur gleichen Datenzeile hinzufügen.

(2) Eine Transaktion, die einer bestimmten Zeile eine gemeinsame Sperre hinzufügt, kann die Zeilendaten möglicherweise nicht unbedingt ändern, da andere Transaktionen der Zeile möglicherweise auch eine gemeinsame Sperre oder eine exklusive Sperre hinzufügen Auf die angegebene Zeile kann die Datenzeile mit einer exklusiven Sperre geändert werden

Tabellensperre

Sperren auf Tabellenebene wird hauptsächlich von einigen nicht-transaktionalen Speicher-Engines wie MyISAM, MEMORY und CSV verwendet.

Tabellensperren werden im Allgemeinen bei der Datenmigration verwendet.

Absichtssperre: gemeinsame Absichtssperre und exklusive Absichtssperre

Die Voraussetzung für das Hinzufügen einer gemeinsamen Sperre zu einer Zeile ist: Die Tabelle, in der sich die Daten der Zeile befinden, erhält zuerst die gemeinsame Absichtssperre.

Voraussetzung für das Hinzufügen einer exklusiven Sperre zu einer Zeile ist, dass die Tabelle, in der sich die Daten der Zeile befinden, zuerst die vorgesehene exklusive Sperre erhält.

Hinweis: Intention Shared Locks und Intention Exclusive Locks sind Tabellensperren und können nicht manuell erstellt werden.

Warum müssen Sie eine Absichtssperre hinzufügen

意向锁是为了告知mysql该表已经存在数据被加锁，而不需要逐行扫描是否加锁，提搞加锁的效率。

单个表锁定

lock tables saas.goods read,saas.account write;　　// 给saas库中的goods表加读锁,account表加写锁unlock tables;   //解锁

全局表锁定

FLUSH TABLES WITH READ LOCK; 　　// 所有库所有表都被锁定只读unlock tables;         //解锁

注意： 在客户端和数据库断开连接时，都会隐式的执行unlock tables。如果要让表锁定生效就必须一直保持连接。

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
  行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；

行锁

• 行锁是锁一行或者多行记录
• MySQL的行锁是基于索引，行锁是加在索引上，而不是加在行记录上的。
  
  如上图所示，数据库中有1个主键索引和1个普通索引，图中的sql语句是基于普通索引查询，命中4条记录，此时一把行锁就锁定两条记录，而其他事务修改这两条记录中的任意一条，都会一直阻塞【获取锁的事务没有执行commit之前】，下图就是上图没有执行commit语句时的情况。
  

行锁升级为表锁（行锁实际是给索引加锁，如果没用索引而全表扫描，则会给全表加锁）

上图中where条件中，虽然template_name建立普通索引，但使用or关键字，导致template_name的索引失效，从而进行了全表扫描，锁定了整张表。

修改、删除某一行记录，且未提交事务时，该行会一直被行锁锁定

窗口1中删除某一行，但没有提交。窗口2中更新该行会一直处于阻塞中。

记录锁

• 行锁：行锁是命中索引，一把锁锁的是一张表的一条记录或多条记录
• 记录锁：记录锁是在行锁的衍生锁，记录锁锁的是表中的某一条记录，记录锁出现的条件必须是：精确命中索引，且索引是唯一索引（比如主键id、唯一索引列）。

间隙锁（Gap Locks）

经典参考文章

间隙锁的作用：防止幻读

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止止间隙内有新数据被插入
（2）防止范围内已存在的数据被更新

innodb自动使用间隙锁的条件

（1）数据隔离级别必须为可重复读
（2）检索条件必须使用索引（没有使用索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加）

间隙锁锁定的区域

根据检索条件向左寻找最靠近检索条件的记录值A，作为左区间，向右寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B）。下图中，where number=5的话，那么间隙锁的区间范围为[4,11]；

session 1:start  transaction ;触发间隙锁的方式1：select  * from news where number=4 for update ;触发间隙锁的方式2：update news set number=3 where number=4; session 2:start  transaction ;insert into news value(2,4);#（阻塞）insert into news value(2,2);#（阻塞）insert into news value(4,4);#（阻塞）insert into news value(4,5);#（阻塞）insert into news value(7,5);#（执行成功）insert into news value(9,5);#（执行成功）insert into news value(11,5);#（执行成功）

next-key锁【临键锁】

next-key锁其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身。InnoDB默认加锁方式是next-key 锁。

select * from news where number=4 for update ;

next-key锁锁定的范围为间隙锁+记录锁，即区间（2，4），（4，5）加间隙锁，同时number=4的记录加记录锁，即next-key锁的锁定的范围为（2,4],(4,5]。

记录锁、间隙锁、临间锁的区别

update news  set number=0 where id>15

sql默认加的是next-key锁。根据上图，next-key锁的区间为（-∞,1],（1,5]，（5,9]，（9，11]，(11,+∞），上面id>15，实际上next-key锁是加在[11，+∞）这个范围内，而不是（15，+∞）这个范围内。注意：需要使用锁的字段必须加索引，因为锁是加在索引上的，没有索引则加的表锁。

相关免费学习推荐：mysql数据库(视频)

Das obige ist der detaillierte Inhalt vonÜberarbeitung des MySQL-Sperrmechanismus. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!