Quel est le concept du mécanisme de verrouillage MySQL

Verrouillage MySQL :

Pour garantir l'exactitude des données dans un environnement multithread, il est généralement nécessaire d'utiliser un mécanisme de synchronisation. La synchronisation équivaut au verrouillage. Le but du verrou est que lorsqu'un thread exploite des données, les autres threads doivent attendre, ce qui peut apporter les avantages suivants. 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 par de nombreux utilisateurs. Comment assurer 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 dispose de moteurs. 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 d'assurer 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 lors d'accès simultanés.

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, les 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. Plusieurs transactions peuvent lire les mêmes données en même temps et se verrouiller en même temps pour éviter de s'affecter mutuellement, mais elles ne peuvent pas modifier les enregistrements de données.

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. Dans le cas d'une concurrence élevée, la granularité du verrouillage est faible, ce qui est bénéfique pour réduire la probabilité de conflits de verrouillage, mais il faut prêter attention à la vitesse de l'opération de verrouillage pour éviter une surcharge excessive, et l'apparition d'un blocage doit être évité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 :

est très optimiste. Chaque fois que vous exploitez les données, vous pensez que personne ne les modifiera, donc vous ne les verrouillez pas.

Mais lors de la mise à jour, il sera jugé si les données ont été modifiées pendant cette période.

Les utilisateurs doivent l'implémenter eux-mêmes. La préemption simultanée des ressources ne se produira pas. Ce n'est que lors de la soumission de l'opération que l'intégrité des données est vérifiée pour toute violation.

Implémentation simple du verrouillage optimiste :

Idée d'implémentation : ajouter des marqueurs pour comparer, s'ils sont identiques, ils seront exécutés, s'ils sont différents, ils ne seront pas exécutés

方式一：版本号

给数据表中添加一个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

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

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

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

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

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

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

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!