Comment garantir l'intégrité des données avec des requêtes de base de données simultanées élevées ? Explication détaillée du verrouillage MySQL/InnoDB

Cet article est une compréhension des concepts de verrous optimistes, de verrous pessimistes, de verrous partagés, de verrous exclusifs, de verrous de ligne, de verrous de table et de blocages dans MySQL/InnoDB. Ceux-ci sont souvent rencontrés dans les entretiens, tels que les requêtes de base de données hautement simultanées. . , comment garantir l'intégrité des données ? Aujourd'hui, j'ai examiné les informations et résumé les points de connaissances sur le verrouillage dans MySQL/InnoDB, afin que tout le monde ne le trouve pas encombrant et compliqué. Si vous le trouvez utile, continuez à le partager. apache php mysql

Remarque : MySQL est un système de base de données qui prend en charge les moteurs de stockage de plug-ins. Toutes les introductions de cet article sont basées sur le moteur de stockage InnoDB. Les performances des autres moteurs seront assez différentes.

Vue du moteur de stockage

MySQL fournit aux développeurs la fonction d'interroger le moteur de stockage. J'utilise MySQL5.6.4 ici. Vous pouvez utiliser :

SHOW ENGINES

begin！

Le verrouillage optimiste

est mis en œuvre à l'aide du mécanisme d'enregistrement de la version des données (Version) .C'est la méthode de mise en œuvre de verrouillage optimiste la plus couramment utilisée. Qu'est-ce qu'une version de données ? Il s'agit d'ajouter un identifiant de version aux données, généralement en ajoutant un champ numérique « version » à la table de la base de données. Lors de la lecture des données, la valeur du champ version est lue ensemble à chaque fois que les données sont mises à jour, la valeur de la version est augmentée de 1. Lorsque nous soumettons une mise à jour, nous comparons les informations de version actuelle de l'enregistrement correspondant dans la table de base de données avec la valeur de version extraite pour la première fois si le numéro de version actuel de la table de base de données est égal à la valeur de version extraite pour la première fois. la première fois, elles seront mises à jour. Dans le cas contraire, elles seront considérées comme des données expirées.

Exemple

1. Conception de table de base de données

Trois champs, à savoir

id,value、version

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

2. vous mettez à jour le champ de valeur dans le tableau, afin d'éviter les conflits, vous devez faire ceci

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

Verrouillage pessimiste

La contrepartie du verrouillage optimiste est le verrouillage pessimiste. Le verrouillage pessimiste signifie que lors de l'exploitation des données, on pense que cette opération provoquera un conflit de données, donc chaque opération doit obtenir le verrou pour fonctionner sur les mêmes données. Ceci est très similaire à la synchronisation en Java, donc le verrouillage pessimiste nécessite Cela prend plus de temps. . De plus, correspondant au verrouillage optimiste, le verrouillage pessimiste est implémenté par la base de données elle-même. Lorsque nous avons besoin de l'utiliser, nous pouvons appeler directement les instructions pertinentes de la base de données.

En parlant de cela, deux autres concepts de verrous impliqués dans le verrouillage pessimiste ressortent. Ce sont les verrous partagés et les verrous exclusifs.

Les verrous partagés et les verrous exclusifs sont des implémentations différentes de verrous pessimistes , qui appartiennent tous deux à la catégorie des verrous pessimistes.

Utilisation, exemple de verrouillage exclusif

Pour utiliser le verrouillage pessimiste, nous devons désactiver l'attribut autocommit de la base de données mysql, car MySQL utilise le mode autocommit par défaut, c'est-à-dire , Lorsque vous effectuez une opération de mise à jour, MySQL soumettra les résultats immédiatement.

Nous pouvons utiliser la commande pour définir MySQL en mode non-autocommit :

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;

verrouillage partagé

verrouillage partagé, également connu sous le nom de

verrouillage en lecture verrouillage en lecture , est le verrou créé par l'opération de lecture. D'autres utilisateurs peuvent lire les données simultanément, mais aucune transaction ne peut modifier les données (acquérir un verrou exclusif sur les données) tant que tous les verrous partagés n'ont pas été libérés.

Si la transaction T ajoute un verrou partagé aux données A, les autres transactions ne peuvent ajouter que des verrous partagés à A et ne peuvent pas ajouter de verrous exclusifs. La transaction qui obtient le verrou partagé ne peut que lire les données, mais ne peut pas modifier les données

Ouvrez la première fenêtre de requête

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

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

puis mettez à jour les données avec l'identifiant 1 dans une autre fenêtre de requête

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

À ce moment, l'interface d'opération entre dans un état bloqué après le délai d'attente, un message d'erreur s'affiche

Si

est exécuté avant le délai d'attente, cette instruction de mise à jour réussira. . commit

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

Après l'ajout du verrou partagé, un message d'erreur s'affiche également

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

Ajouter

LOCK IN SHARE MODE après l'instruction de requête, Mysql le fera répondre à la requête Un verrou partagé est ajouté à chaque ligne de l'ensemble de résultats. Lorsqu'aucun autre thread n'utilise un verrou exclusif sur une ligne de l'ensemble de résultats de la requête, il peut demander avec succès un verrou partagé, sinon il sera bloqué. D'autres threads peuvent également lire des tables qui utilisent des verrous partagés, et ces threads lisent la même version des données. Après avoir ajouté un verrou partagé à

, un verrou exclusif sera automatiquement ajouté à la déclaration

. update,insert,delete

Verrouillage exclusif

Le verrouillage exclusif du verrouillage exclusif (également appelé verrouillage de l'écrivain) est également appelé

verrouillage en écriture.

Le verrouillage exclusif est une implémentation du verrouillage pessimiste qui a également été introduit ci-dessus.

Si la transaction 1 ajoute un verrou X à l'objet de données A, la transaction 1 peut lire ou modifier A, et les autres transactions ne peuvent pas ajouter de verrous à A jusqu'à ce que la transaction 1 libère le verrou sur A. Cela garantit que les autres transactions ne pourront plus lire et modifier A jusqu'à ce que la transaction 1 libère le verrou sur A. Les verrous exclusifs bloqueront tous les verrous exclusifs et les verrous partagés

Pourquoi devons-nous ajouter des verrous en lecture pour la lecture : pour empêcher que des données soient ajoutées aux verrous en écriture par d'autres threads lors de la lecture,

Comment à utiliser : ajoutez simplement

après l'instruction qui doit être exécutée for update

Verrouillage de ligne

Le verrouillage de ligne est divisé en

verrouillage partagé et verrouillage exclusif , compris littéralement, consiste à verrouiller une certaine ligne, c'est-à-dire à verrouiller un enregistrement.

注意：行级锁都是基于索引的，如果一条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数据库锁定机制详细介绍

相关视频：

数据库设计那些事

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!