MySQL Werfen Sie einen Blick auf die Vergangenheit und lernen Sie das Neue kennen – Sperren in der Innodb-Speicher-Engine

Ich bin in letzter Zeit auf viele Schlossprobleme gestoßen. Nachdem ich sie gelöst habe, habe ich die Bücher über Schlösser sorgfältig gelesen. Sie sind wie folgt organisiert:

1 , Art des Schlosses

Innodb Speicher-Engine-Implementierung Die folgenden 2 Standardsperren auf Zeilenebene werden bereitgestellt:

? Gemeinsame Sperre (S lock) , ermöglicht der Transaktion das Lesen einer Datenzeile.

? Exklusive Sperre (X-Sperre), die einer Transaktion das Löschen oder Aktualisieren einer Datenzeile ermöglicht.

Wenn eine Transaktion die gemeinsame Sperre für Zeile r erhält, dann wird eine weitere The Die Transaktion kann auch sofort die gemeinsame Sperre für Zeile r erwerben, da der Lesevorgang die Daten für Zeile r nicht ändert Gehäuseschloss kompatibel. Wenn eine Transaktion jedoch eine exklusive Sperre für Zeile r erhalten möchte, muss sie warten, bis die Transaktion die gemeinsame Sperre für Zeile r—In diesem Fall sind die Schlösser inkompatibel. Die Kompatibilität zwischen den beiden ist in der folgenden Tabelle dargestellt:

X KonfliktKonfliktS KonfliktKompatibel

2, Sperrverlängerung

InnodbDie Speicher-Engine unterstützt Sperren mit mehreren Granularitäten, wodurch Sperren auf Zeilenebene und Sperren auf Tabellenebene gleichzeitig vorhanden sein können. Um Sperrvorgänge mit unterschiedlichen Granularitäten zu unterstützen, unterstützt die InnoDB-Speicher-Engine eine zusätzliche Sperrmethode, nämlich die Absichtssperre. Absichtssperren sind Sperren auf Tabellenebene, die in erster Linie dazu dienen, die Art der Sperre offenzulegen, die für die nächste Zeile innerhalb einer Transaktion angefordert wird. Es ist auch in zwei Typen unterteilt:

? Intention Shared Lock (IS Lock), die Transaktion möchte bestimmte Zeilen in einem erhalten Tabellenfreigabesperre.

? Absichtliche exklusive Sperre (IX-Sperre), die Transaktion möchte eine exklusive Sperre für bestimmte Zeilen in einer Tabelle erhalten.

Da InnoDB Sperren auf Zeilenebene unterstützt, funktionieren Absichtssperren eigentlich nicht Die Assembly blockiert alle Anfragen außer dem vollständigen Tabellen-Scan. Gemeinsame Sperren, exklusive Sperren, gemeinsame Absichtssperren und exklusive Absichtssperren sind alle miteinander kompatibel / sich gegenseitig ausschließende Beziehungen, die durch eine Kompatibilitätsmatrix (y bedeutet kompatibel, n bedeutet inkompatibel ), wie unten gezeigt :

Kompatibilität von exklusiven Schlössern und gemeinsamen Schlössern
	X Exklusives Schloss	S 🎜>
Exklusive Sperre
Gemeinsame Sperre

IX IS X KonfliktKonfliktKonfliktKonfliktS Konflikt

	X Exklusives Schloss	S 🎜>	Absichtliche exklusive Sperre	Absichtliche gemeinsame Sperre
Exklusive Sperre
Gemeinsame Sperre		Kompatibel	Konflikt	Kompatibel mit
IX Intention Exclusive Lock	Konflikt	Konflikt	Kompatibel	Kompatibel mit
IS Intention Shared Lock	Konflikt	Kompatibel	Kompatibel	Kompatibel

  Analyse: Die gegenseitige Kompatibilitätsbeziehung zwischen X und SSchritt 1 wurde beschrieben. Die gegenseitigen Beziehungen zwischen IX und IS sind alle kompatibel auch gut. Verstehen Sie, denn sie sind nur „absichtlich“ und befinden sich noch im JJ Sie haben nicht wirklich etwas getan, also sind sie kompatibel;

links Die nächsten sind

X und IX, >IS, S und IX, S und IS, wir können Leiten Sie diese vier Beziehungssätze aus den Beziehungen von X und S ab.

Einfach ausgedrückt: X und IX der Beziehung zwischen 🎜> und X . Warum? Denn nachdem eine Transaktion die Sperre IX erworben hat, hat sie das Recht, die Sperre X zu erwerben. Wenn X und IX kompatibel sind, erhalten beide Transaktionen die Sperrsituation X Dies widerspricht dem, was wir darüber wissen, dass X und X sich gegenseitig ausschließen, also X und IX können nur eine sich gegenseitig ausschließende Beziehung haben. Die verbleibenden drei Beziehungssätze sind ähnlich und können auf die gleiche Weise abgeleitet werden.

3

, simulierte Schleusenszene

Vor InnoDB Plugin konnten wir nur SHOW FULL PROCESSLIS und SHOW ENGINE passieren INNODB STATUS, um die aktuelle Datenbankanforderung anzuzeigen und dann die Sperrsituation in der Transaktion zu bestimmen. In der neuen Version des InnoDB Plugin werden 3 im information_schema hinzugefügt Bibliothekstabelle, INNODB_LOCKS, INNODB_TRX, INNODB_LOCK_WAITS. Mithilfe dieser 3 Tabellen können Sie aktuelle Transaktionen einfacher überwachen und mögliche Sperrprobleme analysieren. Wenn die Datenbank normal läuft, sind diese 3 Tabellen leer und enthalten keine Datensätze.

3.1, offene Transaktion t1, t2, Simulieren Sie die Sperre

, um 2Sitzungsfenster zu öffnen, und öffnen Sie 2 Transaktionen t1 und t2.

Öffnen Sie die Transaktion im ersten Fenster t1Führen Sie einen Sperrvorgang aus, wie folgt t1Transaktionsfensterschnittstelle:

mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
  开始执行锁定操作
mysql> select * from test.t1 where a<5 for update;
+---+----+----+
| a | b  | c  |
+---+----+----+
| 1 | c2 | c2 |
| 2 | a  |    |
| 3 | r5 | r3 |
| 4 | r4 | r5 |
+---+----+----+
4 rows in set (0.00 sec)

mysql>

这个时候，事务t1已经锁定了表t1的所有a40dad1041c7e7f22fd5f8b283c951c70 update t1 set a=111 where a=1;FEHLER 1205 (HY000): Sperrwartezeitüberschreitung; versuchen Sie, die Transaktion neu zu startenmysql>

Zu diesem Zeitpunkt Sitzung

B

2

wird beendet

Update t1 set a=111 where a=1;

'sdmlAnforderungsvorgang. 6

, automatische Inkrementierung und Sperre

自增长在数据库中是非常常见的一种属性，在Innodb的存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对有自增长字段的表进行insert时候，这个计数器会被初始化，执行如下的sql语句来得到计数器的值。

SELECT MAX(auto_inc_col) FROM tablename FOR UPDATE;

插入操作会依据这个自增长的计数器值+1赋予自增长列，这个实现方式称为AUTO-INC Locking，这种锁其实是一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的sql语句后立即释放。

 

从mysql 5.1.22版本开始，提供了一些轻量级互斥的自增长实现机制，这种机制大大提高自增长值插入的性能。还提供了一个参数innodb_autoinc_lock_mode，默认值为1.

 

自增长的分类：

在mysql的innodb表中，自增长列必须是索引，而且必须为索引的第一列，如果是第二个列会报错如下所示：

mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>
mysql> CREATE TABLE t(a INT AUTO_INCREMENT ,b VARCHAR(10),PRIMARY KEY (a),KEY (b,a));
Query OK, 0 rows affected (0.01 sec)
 
mysql>

而在myisam表中，则没有这样的限制，如下所示：

mysql>  CREATE TABLE t_myisam(a INT AUTO_INCREMENT ,b VARCHAR(10),KEY (b,a))engine=myisam;
Query OK, 0 rows affected (0.01 sec)

 

mysql>

 

7，MySQL外键和锁

在innodb存储引擎中，对于一个外键列，如果没有显式的针对这个列添加索引Innodb存储引擎会自动的对其添加一个索引，这样可以避免表锁，这点比oracle做的较好一些，oracle需要自己手动添加外键锁。

 以上就是MySQL 温故而知新--Innodb存储引擎中的锁的内容，更多相关内容请关注PHP中文网（www.php.cn）！