Wie kann die Datenintegrität bei vielen gleichzeitigen Datenbankanforderungen sichergestellt werden? Ausführliche Erläuterung der MySQL/InnoDB-Sperre

In diesem Artikel werden die Konzepte von optimistischen Sperren, pessimistischen Sperren, gemeinsam genutzten Sperren, exklusiven Sperren, Zeilensperren, Tabellensperren und Deadlocks in MySQL/InnoDB erläutert, die häufig in Interviews auftreten, beispielsweise bei hohen gleichzeitigen Datenbankanforderungen . , wie kann die Datenintegrität sichergestellt werden? Heute habe ich die Informationen überprüft und die Wissenspunkte zum Sperren in MySQL/InnoDB zusammengefasst, damit es nicht für alle umständlich und chaotisch ist. Wenn Sie es nützlich finden, teilen Sie es bitte weiter. Apache PHP MySQL

Hinweis: MySQL ist ein Datenbanksystem, das Plug-in-Speicher-Engines unterstützt. Alle Einführungen in diesem Artikel basieren auf der InnoDB-Speicher-Engine. Die Leistung anderer Engines wird erheblich unterschiedlich sein.

Speicher-Engine-Ansicht

MySQL bietet Entwicklern die Funktion, die Speicher-Engine abzufragen. Ich verwende hier MySQL5.6.4 >

SHOW ENGINES

begin！Optimistische Sperre

wird mithilfe des Datenversionsaufzeichnungsmechanismus (Version) implementiert Dies ist die am häufigsten verwendete Implementierungsmethode für optimistisches Sperren. Was ist eine Datenversion? Dabei wird den Daten eine Versionskennung hinzugefügt, normalerweise durch Hinzufügen eines numerischen „Version“-Felds zur Datenbanktabelle. Beim Lesen von Daten wird der Wert des Versionsfelds zusammen gelesen. Bei jeder Aktualisierung der Daten wird der Versionswert um 1 erhöht. Wenn wir ein Update einreichen, vergleichen wir die aktuellen Versionsinformationen des entsprechenden Datensatzes in der Datenbanktabelle mit dem zum ersten Mal entnommenen Versionswert. Wenn die aktuelle Versionsnummer der Datenbanktabelle mit dem entnommenen Versionswert übereinstimmt Beim ersten Mal werden sie aktualisiert. Andernfalls gelten sie als abgelaufene Daten.

Beispiel

1. Datenbanktabellendesign

Drei Felder, nämlich

select id,value,version from TABLE where id=#{id}

id,value、version2 Um Konflikte im Wertefeld in der Tabelle zu vermeiden, müssen Sie wie folgt vorgehen

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};

Pessimistische Sperre

Das Gegenstück zur optimistischen Sperre ist die pessimistische Sperre. Pessimistische Sperre bedeutet, dass bei der Verarbeitung von Daten davon ausgegangen wird, dass dieser Vorgang einen Datenkonflikt verursacht. Daher muss jede Operation eine Sperre erhalten, um mit denselben Daten arbeiten zu können. Dies ist der Synchronisierung in Java sehr ähnlich, daher ist eine pessimistische Sperre erforderlich Es dauert länger . Darüber hinaus wird pessimistisches Sperren entsprechend dem optimistischen Sperren von der Datenbank selbst implementiert. Wenn wir es verwenden müssen, können wir die relevanten Anweisungen der Datenbank direkt aufrufen.

Apropos, zwei weitere Schlosskonzepte, die mit pessimistischen Sperren zu tun haben, sind gemeinsame Sperren und exklusive Sperren.

Gemeinsame Sperren und exklusive Sperren sind unterschiedliche Implementierungen pessimistischer Sperren

, die beide zur Kategorie der pessimistischen Sperren gehören.

Verwenden Sie ein exklusives Sperrbeispiel

Um pessimistisches Sperren zu verwenden, müssen wir das Autocommit-Attribut der MySQL-Datenbank deaktivieren, da MySQL standardmäßig den Autocommit-Modus verwendet , Wenn Sie einen Aktualisierungsvorgang durchführen, übermittelt MySQL die Ergebnisse sofort.

Wir können den Befehl verwenden, um MySQL in den Nicht-Autocommit-Modus zu versetzen:

set autocommit=0;

# 设置完autocommit后，我们就可以执行我们的正常业务了。具体如下：

# 1. 开始事务

begin;/begin work;/start transaction; (三者选一就可以)

# 2. 查询表信息

select status from TABLE where id=1 for update;

# 3. 插入一条数据

insert into TABLE (id,value) values (2,2);

# 4. 修改数据为

update TABLE set value=2 where id=1;

# 5. 提交事务

commit;/commit work;

Shared lock

Shared lock wird auch

read lock read lock

genannt , die gelesen wird Die durch den Vorgang erstellte Sperre. Andere Benutzer können die Daten gleichzeitig lesen, aber keine Transaktion kann die Daten ändern (eine exklusive Sperre für die Daten erwerben), bis alle gemeinsamen Sperren aufgehoben wurden. Wenn Transaktion T eine gemeinsame Sperre zu Daten A hinzufügt, können andere Transaktionen nur gemeinsame Sperren zu A und keine exklusiven Sperren hinzufügen. Die Transaktion, die die gemeinsame Sperre erhält, kann Daten nur lesen, aber nicht ändern.

Öffnen Sie das erste Abfragefenster

begin;/begin work;/start transaction;  (三者选一就可以)

SELECT * from TABLE where id = 1  lock in share mode;

Aktualisieren Sie dann in einem anderen Abfragefenster die Daten mit der ID 1

update  TABLE set name="www.souyunku.com" where id =1;

Zu diesem Zeitpunkt befindet sich die Betriebsschnittstelle in einem hängenden Zustand. Nach dem Timeout wird eine Fehlermeldung angezeigt.

Wenn

vor dem Timeout ausgeführt wird, ist diese Aktualisierungsanweisung erfolgreich.

[SQL]update  test_one set name="www.souyunku.com" where id =1;
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction

commitNach dem Hinzufügen der gemeinsamen Sperre wird auch eine Fehlermeldung angezeigt.

update  test_one set name="www.souyunku.com" where id =1 lock in share mode;

[SQL]update  test_one set name="www.souyunku.com" where id =1 lock in share mode;
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

Fügen Sie

nach der Abfrageanweisung hinzu. MySQL fügt eine gemeinsame Sperre hinzu, wenn keine andere vorhanden ist Der Thread verwendet eine exklusive Sperre für jede Zeile im Abfrageergebnissatz. Er kann die gemeinsame Sperre erfolgreich beantragen, andernfalls wird er blockiert. Andere Threads können Tabellen auch mithilfe gemeinsamer Sperren lesen, und diese Threads lesen dieselbe Datenversion. Nach dem Hinzufügen einer gemeinsamen Sperre zu LOCK IN SHARE MODE wird automatisch eine exklusive Sperre zur

-Anweisung hinzugefügt.

update,insert,deleteExklusive Sperre

Exklusive Sperre (auch Schreibsperre genannt) wird auch

Schreibsperre

genannt.

Exklusive Sperre ist eine Implementierung der pessimistischen Sperre, die ebenfalls oben eingeführt wurde

. Wenn Transaktion 1 eine X-Sperre zum Datenobjekt A hinzufügt, kann Transaktion 1 A lesen oder ändern, und andere Transaktionen können keine Sperren zu A hinzufügen, bis Transaktion 1 die Sperre für A aufhebt. Dies garantiert, dass andere Transaktionen A nicht mehr lesen und ändern können, bis Transaktion 1 die Sperre für A aufhebt. Exklusive Sperren blockieren alle exklusiven Sperren und gemeinsam genutzten Sperren

Warum müssen wir zum Lesen Lesesperren hinzufügen: Um zu verhindern, dass Daten beim Lesen von anderen Threads zu Schreibsperren hinzugefügt werden,

Wie Zur Verwendung: Fügen Sie einfach

nach der auszuführenden Anweisung hinzu

for updateZeilensperre

Zeilensperre ist in

gemeinsame Sperre

und exklusive Sperre bedeutet im wörtlichen Sinne das Sperren einer bestimmten Zeile, also das Sperren eines Datensatzes.

注意：行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。

共享锁：

名词解释：共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁，除此之外其他任何类型的锁都不能再加了。

SELECT * from TABLE where id = "1"  lock in share mode;  结果集的数据都会加共享锁

排他锁：

名词解释：若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。

select status from TABLE where id=1 for update;

可以参考之前演示的共享锁,排它锁语句

由于对于表中,id字段为主键，就也相当于索引。执行加锁时，会将id这个索引为1的记录加上锁，那么这个锁就是行锁。

表锁

如何加表锁

innodb 的行锁是在有索引的情况下,没有索引的表是锁定全表的.

Innodb中的行锁与表锁

前面提到过，在Innodb引擎中既支持行锁也支持表锁，那么什么时候会锁住整张表，什么时候或只锁住一行呢？
只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

行级锁都是基于索引的，如果一条SQL语句用不到索引是不会使用行级锁的，会使用表级锁。行级锁的缺点是：由于需要请求大量的锁资源，所以速度慢，内存消耗大。

死锁

死锁（Deadlock） 
所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。

解除正在死锁的状态有两种方法：

第一种：

1.查询是否锁表

show OPEN TABLES where In_use > 0;

2.查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

show processlist

3.杀死进程id（就是上面命令的id列）

kill id

第二种：

1：查看当前的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看当前锁定的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看当前等锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

杀死进程

kill 线程ID

如果系统资源充足，进程的资源请求都能够得到满足，死锁出现的可能性就很低，否则就会因争夺有限的资源而陷入死锁。其次，进程运行推进顺序与速度不同，也可能产生死锁。
产生死锁的四个必要条件：

（1） 互斥条件：一个资源每次只能被一个进程使用。
（2） 请求与保持条件：一个进程因请求资源而阻塞时，对已获得的资源保持不放。
（3） 不剥夺条件:进程已获得的资源，在末使用完之前，不能强行剥夺。
（4） 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

虽然不能完全避免死锁，但可以使死锁的数量减至最少。将死锁减至最少可以增加事务的吞吐量并减少系统开销，因为只有很少的事务回滚，而回滚会取消事务执行的所有工作。由于死锁时回滚而由应用程序重新提交。

下列方法有助于最大限度地降低死锁：

（1）按同一顺序访问对象。
（2）避免事务中的用户交互。
（3）保持事务简短并在一个批处理中。
（4）使用低隔离级别。
（5）使用绑定连接。

end！

相关文章：

数据库并发事务控制 二：mysql数据库锁机制

Mysql数据库锁定机制详细介绍

相关视频：

数据库设计那些事

Das obige ist der detaillierte Inhalt vonWie kann die Datenintegrität bei vielen gleichzeitigen Datenbankanforderungen sichergestellt werden? Ausführliche Erläuterung der MySQL/InnoDB-Sperre. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!