Analyse détaillée du mécanisme de verrouillage MySQL

Cet article vous apporte des connaissances pertinentes sur mysql, qui présente principalement les différents problèmes de mécanisme de verrouillage dans MySQL. Les verrous sont utilisés par la base de données pour assurer la cohérence des données et utiliser diverses ressources partagées pour être utilisées simultanément. ordonné lors de la visite. J'espère que cela sera utile à tout le monde.

Apprentissage recommandé : Tutoriel mysql

Verrouillage MySQL :

Comment garantir l'exactitude des données en multi-threading ? C'est vrai, grâce à la synchronisation. La synchronisation équivaut au verrouillage. Quels sont les avantages de l’ajout d’une serrure ? Lorsqu'un thread manipule réellement des données, les autres threads ne peuvent qu'attendre. Lorsqu'un thread termine son exécution, le verrou est libéré. Seuls les autres threads peuvent effectuer des opérations !

Ensuite, la fonction des verrous dans notre base de données MySQL est similaire. Dans l'isolement du traitement des transactions, des problèmes tels que des lectures sales, des lectures non répétables et des lectures fantômes peuvent survenir. Par conséquent, le rôle des verrous peut également résoudre ces problèmes !

Dans une base de données, les données sont une ressource partagée et accessible par de nombreux utilisateurs. Comment garantir la cohérence et l'efficacité de l'accès simultané aux données est un problème que toutes les bases de données doivent résoudre En raison des caractéristiques de sa propre architecture, MySQL a le pouvoir. les moteurs sont tous conçus avec des mécanismes de verrouillage pour des scénarios spécifiques, de sorte que les différences entre les moteurs conduisent à des mécanismes de verrouillage très différents.

Mécanisme de verrouillage :

Afin de garantir la cohérence des données, la base de données utilise une règle conçue en utilisant diverses ressources partagées pour devenir ordonnée en cas d'accès simultané.

Par exemple : lors de l'achat d'un produit sur un site de commerce électronique, il n'y a qu'un seul produit dans la liste de produits, et à ce moment-là, deux personnes l'achètent en même temps, donc qui peut l'acheter est une question clé.

Les transactions seront utilisées ici pour effectuer une série d'opérations :

Récupérez d'abord les données de l'article de la table des produits

Puis insérez la commande

Après le paiement, insérez les informations du tableau de paiement

Mettre à jour la quantité des articles dans la table des produits

Dans le processus ci-dessus, des verrous peuvent être utilisés pour protéger les informations sur les données de quantité de produit et réaliser l'isolement, c'est-à-dire que seul le premier utilisateur est autorisé à terminer l'ensemble du processus d'achat, tandis que les autres utilisateurs ne peuvent qu'attendre, résolvant ainsi le problème. problème de conflit en simultanéité.

Classification des serrures :

Classification par opération :

Serrure partagée : également appelée serrure en lecture. Pour les mêmes données, plusieurs opérations de lecture de transactions peuvent être verrouillées en même temps sans s'affecter mutuellement, mais les enregistrements de données ne peuvent pas être modifiés.

Verrou exclusif : également appelé verrouillage en écriture. Avant que l'opération en cours ne soit terminée, elle bloquera la lecture et l'écriture des autres opérations. Classification par granularité :

Verrouillage au niveau de la table : Pendant l'opération, la table entière sera verrouillée. La surcharge est faible et le verrouillage est rapide ; aucun blocage ne se produira ; la force de verrouillage est forte, la probabilité de conflit de verrouillage est élevée et la concurrence est la plus faible. Préférez le moteur de stockage MyISAM !

Verrouillage au niveau de la ligne : pendant le fonctionnement, la ligne d'opération en cours sera verrouillée. La surcharge est importante et le verrouillage est lent ; des blocages peuvent se produire ; la granularité du verrouillage est faible, la probabilité de conflits de verrouillage est faible et la concurrence est élevée. Préférez le moteur de stockage InnoDB !

Verrouillage au niveau de la page : la granularité du verrouillage, la probabilité de conflit et le coût de verrouillage se situent entre les verrous de table et les verrous de ligne. Des blocages se produiront et les performances de concurrence sont moyennes.

Classé par usage :

Verrouillage pessimiste : chaque fois que vous interrogez les données, vous pensez que d'autres vont les modifier. C'est très pessimiste, vous les verrouillez donc lors de l'interrogation.

Verrouillage optimiste : chaque fois que vous interrogez les données, vous pensez que d'autres ne les modifieront pas. Vous êtes très optimiste, mais lors de la mise à jour, vous jugerez si d'autres ont mis à jour les données pendant cette période

Verrous pris en charge par différents stockages. moteurs

Sharing Lock :

Plusieurs verrous partagés peuvent être partagés. S'il y a une clé, InnoDB utilise par défaut un verrou de ligne. Sinon, il sera mis à niveau vers un verrou de table. plusieurs fenêtres peuvent modifier les données dans différentes lignes. Si elles se trouvent dans la même ligne, elles doivent le faire. Si vous attendez que le premier commit verrouillé soit soumis, différentes lignes peuvent être modifiées directement, mais si vous souhaitez interroger l'autre, vous devez le faire. pour attendre les commits modifiés ultérieurs. Le verrou disparaît après la soumission

Verrouillage partagé :

SELECT语句 LOCK IN SHARE MODE;

Fenêtre 1 :

- 窗口1
/*
    共享锁：数据可以被多个事务查询，但是不能修改
*/
-- 开启事务
START TRANSACTION;
-- 查询id为1的数据记录。加入共享锁
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
-- 查询分数为99分的数据记录。加入共享锁
SELECT * FROM student WHERE score=99 LOCK IN SHARE MODE;
-- 提交事务
COMMIT;

Fenêtre 2 :

-- 窗口2
-- 开启事务
START TRANSACTION;
-- 查询id为1的数据记录(普通查询，可以查询)
SELECT * FROM student WHERE id=1;
-- 查询id为1的数据记录，并加入共享锁(可以查询。共享锁和共享锁兼容)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
-- 修改id为1的姓名为张三三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME='张三三' WHERE id = 1;
-- 修改id为2的姓名为李四四(修改成功，InnoDB引擎默认是行锁)
UPDATE student SET NAME='李四四' WHERE id = 2;
-- 修改id为3的姓名为王五五(修改失败，InnoDB引擎如果不采用带索引的列加锁。则会提升为表锁)
UPDATE student SET NAME='王五五' WHERE id = 3;
-- 提交事务
COMMIT;

Verrouillage exclusif :

Lorsque le verrouillage exclusif est exécuté, des requêtes ordinaires d'autres transactions peuvent être effectuées, mais aucune opération de verrouillage sont autorisés

-- 标准语法
SELECT语句 FOR UPDATE;

Fenêtre 1 :

-- 窗口1
/*
    排他锁：加锁的数据，不能被其他事务加锁查询或修改
*/
-- 开启事务
START TRANSACTION;
-- 查询id为1的数据记录，并加入排他锁
SELECT * FROM student WHERE id=1 FOR UPDATE;
-- 提交事务
COMMIT;

Fenêtre 2 :

-- 窗口2
-- 开启事务
START TRANSACTION;
-- 查询id为1的数据记录(普通查询没问题)
SELECT * FROM student WHERE id=1;
-- 查询id为1的数据记录，并加入共享锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 LOCK IN SHARE MODE;
-- 查询id为1的数据记录，并加入排他锁(不能查询。因为排他锁不能和其他锁共存)
SELECT * FROM student WHERE id=1 FOR UPDATE;
-- 修改id为1的姓名为张三(不能修改，会出现锁的情况。只有窗口1提交事务后，才能修改成功)
UPDATE student SET NAME='张三' WHERE id=1;
-- 提交事务
COMMIT;

Verrou MyISAM :

Verrou de lecture MyISAM :

myisam est un verrou pour toute la table. Lors de la lecture du verrou, s'il n'est pas déverrouillé, tous. les transactions peuvent être vérifiées, mais aucune autre opération, y compris la propre transaction, ne peut être effectuée

-- 加锁
LOCK TABLE 表名 READ;
-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;

Verrou en écriture MyISAM :

Lors de l'écriture du verrou, tant que les autres transactions ne sont pas déverrouillées, aucune opération ne peut être effectuée et la propre transaction peut être utilisé

-- 标准语法
-- 加锁
LOCK TABLE 表名 WRITE;
-- 解锁(将当前会话所有的表进行解锁)
UNLOCK TABLES;

Verrou pessimiste :

Il est très pessimiste à l'égard des données modifiées par le monde extérieur et pense que les données seront modifiées à tout moment.

Les données doivent être verrouillées pendant tout le traitement des données. Le verrouillage pessimiste repose généralement sur le mécanisme de verrouillage fourni par les bases de données relationnelles.

Les verrous de ligne et les verrous de table sont tous deux des verrous pessimistes, qu'il s'agisse de verrous en lecture ou en écriture.

Verrouillage optimiste :

就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。

但是在更新的时候会去判断在此期间数据有没有被修改。

需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。

乐观锁的简单实现方式：

实现思想：加标记去比较，一样则执行，不同则不执行

方式一：版本号

给数据表中添加一个version列，每次更新后都将这个列的值加1。

读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。

如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。

-- 创建city表
CREATE TABLE city(
    id INT PRIMARY KEY AUTO_INCREMENT,  -- 城市id
    NAME VARCHAR(20),                   -- 城市名称
    VERSION INT                         -- 版本号
);
-- 添加数据
INSERT INTO city VALUES (NULL,'北京',1),(NULL,'上海',1),(NULL,'广州',1),(NULL,'深圳',1);
-- 修改北京为北京市
-- 1.查询北京的version
SELECT VERSION FROM city WHERE NAME='北京';
-- 2.修改北京为北京市，版本号+1。并对比版本号
UPDATE city SET NAME='北京市',VERSION=VERSION+1 WHERE NAME='北京' AND VERSION=1;

方式二：时间戳

和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp

每次更新后都将最新时间插入到此列。

读取数据时，将时间读取出来，在执行更新的时候，比较时间。

如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。

悲观锁和乐观锁使用前提：

对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁。

如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁。

推荐学习：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!