MySQL Learning spricht über die Sperrsituation in InnoDB

In diesem Artikel wird über MySQL gesprochen und die Sperrsituation in InnoDB vorgestellt. Es hat einen gewissen Referenzwert. Freunde in Not können sich darauf beziehen. Ich hoffe, es wird für alle hilfreich sein.

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.01 sec)

Zuerst eine grundlegende Einführung in Sperren

Im Vergleich zu anderen Datenbanken ist der Sperrmechanismus von MySQL relativ einfach, dass verschiedene Speicher-Engines unterschiedliche Sperrmechanismen unterstützen. Beispielsweise verwenden die Speicher-Engines MyISAM und MEMORY Sperren auf Tabellenebene; die InnoDB-Speicher-Engine unterstützt sowohl Sperren auf Zeilenebene (Sperren auf Zeilenebene) als auch Sperren auf Tabellenebene, es wird jedoch standardmäßig Sperren auf Zeilenebene verwendet.

Sperre auf Tabellenebene: geringer Overhead, keine Deadlocks; große Sperrgranularität, höchste Wahrscheinlichkeit von Sperrenkonflikten und niedrigste Parallelität.

Sperre 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.

Sperrtyp auf Zeilenebene:

Datensatzsperre (Datensatzsperre): als Datensatzsperre (Sperren eines einzelnen Datensatzes)

Datensatzsperre sperrt nur den Indexdatensatz, wenn die InnoDB-Speichertabelle erstellt wird Wenn kein Index vorhanden ist, verwendet diese Sperre den impliziten Primärschlüssel zum Sperren, wie unten gezeigt. Lückensperre: Sperren Sie einen Bereich, ohne den Datensatz selbst (sperren Sie nur die Lücke vor den Daten).

Die im Bild unten gezeigte Sperre ist eine GAP-Sperre, die es anderen Transaktionen nicht erlaubt, neue Datensätze in die Lücke vor Indexspalte 8 einzufügen, was dem Intervall (3, 8) entspricht.

Die Lückensperre dient nur dazu, das Einfügen von Phantomdatensätzen zu verhindern

Next-Key-Sperre: Sperren Sie den Datensatz und die Lücke vor dem Datensatz gleichzeitig, d. h. Next-Key Sperre = Aufnahmesperre + Lückensperre.

Zweitens die Klassifizierung von Sperren

Gemeinsame Sperren Gemeinsame Sperren (bezeichnet als S-Sperren, gehören zu Zeilensperren)

Exklusive Sperren (bezeichnet als X-Sperren, gehören zu Zeilensperren)

Intention Shared Locks Share Locks (kurz IS-Sperren, gehören zu Tabellensperren)Intention Exclusive Locks (kurz IX-Sperren, gehören zu Tabellensperren)

AUTO-INC-Sperren (gehören zu Tabellensperren)

Das Folgende ist a Detaillierte Einführung in die einzelnen Sperren: Wir erstellen zunächst eine Innodb-Tabelle. Die SQL lautet wie folgt: Gemeinsame Sperre: Gemeinsame Sperre bedeutet, dass mehrere Transaktionen eine Sperre für dieselben Daten teilen können, und zwar alle Zugriff auf die Datenbank, aber sie können nur gelesen werden.

Transaktion A:

select * from tab_with_index lock in share mode; Daten können abgefragt werden

update tab_with_index set name = 'aa' where id = 1 ;

Hinweis: Die Änderungsanweisung hier wird blockiert und der Vorgang kann erst erfolgreich sein, wenn Transaktion A übermittelt wurde.

Exklusive Sperre

Exklusive Sperren können nicht mit anderen Sperren koexistieren. Wenn eine Transaktion eine exklusive Sperre für eine Datenzeile erwirbt, können andere Transaktionen die Sperre für die Zeile nicht erwerben. Nur die Transaktion, die derzeit die exklusive Sperre erwirbt, kann geändert werden . (Löschen, Aktualisieren, Erstellen sind standardmäßig exklusive Sperren)

Transaktion A:

select * from tab_with_index where id =1 for update;

Transaction B:

select * from tab_with_index where id =1; / /Kann Ergebnisse erhalten

select * from tab_with_index where id =1 for update; // Blocked

select * from tab_with_index where id = 1 lock for share mode; // Blocked

Hinweis: Transaktion B zwei Jede SQL-Anweisung wird blockiert, das heißt, weder die gemeinsame Sperre noch die exklusive Sperre können erhalten werden. Der Vorgang kann erst erfolgreich sein, wenn Transaktion A übermittelt wird.

Intention Shared Lock und Intention Exclusive Lock

Intention Shared Lock: Zeigt an, dass die Transaktion das Hinzufügen einer Shared Lock zur Datenzeile vorbereitet, was bedeutet, dass eine Datenzeile vor dem Hinzufügen zunächst die IS-Sperre der Tabelle erhalten muss eine gemeinsame Sperre.

Beabsichtigte exklusive Sperre: Zeigt an, dass die Transaktion das Hinzufügen einer exklusiven Sperre zur Datenzeile vorbereitet. Das heißt, bevor eine exklusive Sperre zu einer Datenzeile hinzugefügt wird, muss zuerst die IX-Sperre der Tabelle erworben werden.

IS-Sperre und IX-Sperre sind Sperren auf Tabellenebene. Sie werden nur vorgeschlagen, um schnell festzustellen, ob die Datensätze in der Tabelle gesperrt sind, wenn später S-Sperren und X-Sperren auf Tabellenebene hinzugefügt werden, um die Verwendung von Traversal zum Anzeigen zu vermeiden . Gibt es gesperrte Datensätze in der Tabelle? Mit anderen Worten, IS-Sperren sind mit IX-Sperren kompatibel und IX-Sperren sind mit IX-Sperren kompatibel. „Wie MySQL läuft“

Auto-Inkrement-Sperre

Eine spezielle Sperre auf Tabellenebene für Auto-Inkrement-Spalten.

show variables like 'innodb_autoinc_lock_mode'; 
-- 默认值1，代表连续，事务未提交则ID永久丢失

三，InnoDB锁

1、事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

2、并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一下问题：

脏读： 一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”

不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。

幻读： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed		√	√
repeatable read			√
serializable

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

3、InnoDB的行锁模式及加锁方法

共享锁（S） ：又称读锁(lock in share mode)。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。  排他锁（X） ：又称写锁(for update)。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务中是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

4、InnoDB行锁实现方式

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

1、在不通过索引条件查询的时候，innodb使用的是表锁而不是行锁

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_no_index where id = 1;	set autocommit=0 select * from tab_no_index where id =2
select * from tab_no_index where id = 1 for update
	select * from tab_no_index where id = 2 for update;

session1只给一行加了排他锁，但是session2在请求其他行的排他锁的时候，会出现锁等待。原因是在没有索引的情况下，innodb只能使用表锁。

2、创建带索引的表进行条件查询，innodb使用的是行锁

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_with_indexwhere id = 1;	set autocommit=0 select * from tab_with_indexwhere id =2
select * from tab_with_indexwhere id = 1 for update
	select * from tab_with_indexwhere id = 2 for update;

3、由于mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是依然无法访问到具体的数据（这里是表锁）

alter table tab_with_index drop index id;
insert into tab_with_index  values(1,'4');

session1	session2
set autocommit=0	set autocommit=0
select * from tab_with_index where id = 1 and name='1' for update
	select * from tab_with_index where id = 1 and name='4' for update 虽然session2访问的是和session1不同的记录，但是锁的是具体表，所以需要等待锁

Zusammenfassung

Für InnoDB-Tabellen werden in diesem Artikel hauptsächlich die folgenden Inhalte behandelt: (1) Die Zeilensperren von InnoDB basieren auf Indizes. Wenn auf Daten nicht über Indizes zugegriffen wird, verwendet InnoDB Tabellensperren. (2) Unter verschiedenen Isolationsstufen sind der Sperrmechanismus und die konsistente Lesestrategie von InnoDB unterschiedlich.

Nachdem Benutzer die Sperreigenschaften von InnoDB verstanden haben, können sie Sperrkonflikte und Deadlocks durch Design- und SQL-Anpassungen reduzieren, darunter:

• Versuchen Sie, Indizes sorgfältig zu entwerfen und Indizes für den Zugriff auf Daten zu verwenden Das Sperren ist präziser, wodurch die Wahrscheinlichkeit von Sperrkonflikten verringert wird.
• Wählen Sie eine angemessene Transaktionsgröße, und die Wahrscheinlichkeit von Sperrkonflikten bei kleinen Transaktionen ist geringer.
• Beim expliziten Sperren des Datensatzes ist es am besten, eine ausreichende Ebene anzufordern auf einmal Sperren. Wenn Sie beispielsweise Daten ändern möchten, beantragen Sie am besten direkt eine exklusive Sperre, anstatt zuerst eine gemeinsame Sperre zu beantragen, und fordern Sie dann beim Ändern eine exklusive Sperre an.
• Wenn verschiedene Programme auf a zugreifen Bei einer Gruppe von Tabellen sollten sie versuchen, sich auf dasselbe zu einigen. Greifen Sie nacheinander auf die einzelnen Tabellen zu. Greifen Sie bei einer Tabelle möglichst in einer festen Reihenfolge auf die Zeilen zu. Dies kann die Wahrscheinlichkeit eines Deadlocks erheblich verringern.
• Versuchen Sie, gleiche Bedingungen für den Zugriff auf Daten zu verwenden, um die Auswirkungen von Lückensperren auf gleichzeitige Einfügungen zu vermeiden. Beantragen Sie keine Sperren, die über den tatsächlichen Bedarf hinausgehen Bei Abfragen werden keine Sperren angezeigt.
• Bei bestimmten Transaktionen können Tabellensperren verwendet werden, um die Verarbeitungsgeschwindigkeit zu erhöhen oder die Möglichkeit eines Deadlocks zu verringern.

【Verwandte Empfehlungen: MySQL-Video-Tutorial】

Das obige ist der detaillierte Inhalt vonMySQL Learning spricht über die Sperrsituation in InnoDB. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!