Transactions et verrous dans MySQL

Cet article présente en détail les connaissances pertinentes sur les transactions et les verrous de base de données. Principalement, certaines choses conceptuelles peuvent sembler ennuyeuses, mais en tant que programmeur qualifié, vous devriez et devez les maîtriser. Cette connaissance théorique est comme la force interne d'une personne. Lorsque nous codons habituellement, il s'agit d'une force externe. Ce n'est qu'en pratiquant à la fois les compétences internes et externes et en se promouvant mutuellement que nous pouvons atteindre le niveau d'un maître d'arts martiaux. Bon, sans plus tarder, commençons.

Transaction de base de données

Limite de la transaction

Début de la limite de la transaction (début)
Fin de la limite de la transaction (commit) : Soumettez la transaction et enregistrez-la définitivement après avoir été mis à jour par l'état de la base de données des transactions.
Limite de fin anormale de la transaction (rollback) : annulez la transaction et remettez la base de données à l'état initial avant d'exécuter la transaction.

Chaque fois que vous démarrez un programme MySQL.exe, vous obtiendrez une connexion à la base de données distincte. Chaque connexion à la base de données possède une variable globale autocommit, qui représente le mode de transaction actuel. Elle a deux valeurs facultatives :

• 0 : représente le mode de validation manuelle

• 1 : Indique le mode de soumission automatique. La valeur par défaut est

Nous pouvons visualiser et modifier cette valeur.

Quatre caractéristiques des transactions de base de données (ACID) :

• Atomicité : Une transaction est une unité d'opération atomique, et sa modification des données, soit tout exécuter ou ne pas l'exécuter du tout ;

• Cohérent : Les données doivent rester cohérentes lorsque la transaction est démarrée et terminée

• Isolement : La base de données ; le système fournit un certain mécanisme d'isolation pour garantir que les transactions sont exécutées dans un environnement « indépendant » qui n'est pas affecté par les opérations simultanées externes

• Durable : une fois la transaction terminée, sa modification en ; les données sont permanentes et peuvent être conservées même en cas de panne du système.

Niveau d'isolement des transactions

Le niveau d'isolement des transactions de la base de données concerne uniquement si une transaction peut lire les résultats intermédiaires d'autres transactions.

• Lire non validé (lire le contenu non validé)
  À ce niveau d'isolement, toutes les transactions peuvent voir le résultat de l'exécution d'autres transactions non validées. Ce niveau d'isolation est rarement utilisé dans les applications pratiques car ses performances ne sont guère meilleures que les autres niveaux. La lecture de données non validées est également appelée lecture sale.

• Lecture validée
  Il s'agit du niveau d'isolement par défaut pour la plupart des systèmes de bases de données (mais pas celui par défaut de MySQL). Cela répond à la définition simple de l'isolement : une transaction ne peut voir que les modifications apportées par les transactions validées. Ce niveau d'isolement prend également en charge la lecture dite non répétable, car d'autres instances de la même transaction peuvent avoir de nouvelles validations pendant le traitement de cette instance, de sorte que la même sélection peut renvoyer des résultats différents.

• Lecture répétable (relisible)
  Il s'agit du niveau d'isolation des transactions par défaut de MySQL, qui garantit que plusieurs instances de la même transaction verront les mêmes lignes de données lors de la lecture simultanée des données. Cependant, en théorie, cela entraînera un autre problème épineux : la lecture fantôme (Phantom Read). En termes simples, la lecture fantôme signifie que lorsque l'utilisateur lit une certaine plage de lignes de données, une autre transaction insère une nouvelle ligne dans la plage. Lorsque l'utilisateur lit à nouveau les lignes de données dans la plage, il constatera qu'il y a de nouvelles lignes fantômes. " D'ACCORD. Les moteurs de stockage InnoDB et Falcon résolvent ce problème grâce au mécanisme MVCC (Multiversion Concurrency Control).

• Sérialisable (sérialisable)
  Il s'agit du niveau d'isolement le plus élevé. Il résout le problème de lecture fantôme en forçant les transactions à être ordonnées afin qu'elles ne puissent pas entrer en conflit les unes avec les autres. En bref, il ajoute un verrou partagé sur chaque ligne de données lue. À ce niveau, de nombreux délais d'attente et conflits de verrouillage peuvent en résulter.

Plus le niveau d'isolement est élevé, plus les données peuvent être garanties complètes et cohérentes, mais plus l'impact sur les performances de concurrence est grand.
Pour la plupart des applications, vous pouvez effectivement envisager de définir le niveau d'isolement du système de base de données sur Lecture validée, ce qui peut éviter les lectures sales et offrir de meilleures performances de concurrence. Bien que cela entraîne des problèmes de concurrence tels que des lectures non répétables, des lectures gaspillées et des mises à jour perdues de type II, dans des situations individuelles où de tels problèmes peuvent survenir, ils peuvent être contrôlés par l'application à l'aide de verrous pessimistes et de verrous optimistes.

Propagation des transactions

1. PROPAGATION_REQUIRED
   Ajoutez la transaction en cours d'exécution. Si la transaction en cours n'existe pas, alors démarrez une nouvelle transaction. Le comportement de propagation des transactions par défaut de la base de données d'exploitation Spring est propagation_required.

2. PROPAGATION_SUPPORTS
   S'il est actuellement dans une transaction, il s'exécutera comme une transaction. S'il n'est plus dans une transaction, il s'exécutera comme une non-transaction.

3. PROPAGATION_MANDATORY
   Doit être exécuté dans le cadre d'une transaction. En d’autres termes, il ne peut être appelé que par une transaction parent. Sinon, il lèvera une exception.

4. PROPAGATION_REQUIRES_NEW
   Suspendre la transaction en cours et démarrer une nouvelle transaction.

5. PROPAGATION_NOT_SUPPORTED

   Les transactions ne sont actuellement pas prises en charge. S'il s'agit d'une transaction, la transaction en cours sera suspendue et elle s'exécutera selon un comportement non transactionnel.

6. PROPAGATION_NEVER
   ne peut pas être exécuté dans une transaction, et une exception sera levée si elle est exécutée dans une transaction.

7. PROPAGATION_NESTED
   La transaction imbriquée dépend de la transaction parent. Lorsque la transaction parent est validée, elle sera validée. Si la transaction parent est annulée, elle sera annulée.

Verrouillage au niveau de la ligne

Trois types de verrous dans Mysql :

Niveau de la ligne : moteur INNODB, verrouillage des enregistrements de lignes individuelles
Page niveau : le moteur BDB verrouille un groupe adjacent d'enregistrements à la fois.
Niveau table : moteur MyISAM, entendu comme verrouillant toute la table, qui peut être lue en même temps, mais pas écrite.
Les caractéristiques des trois types de verrous peuvent être résumées comme suit :
1) Verrous au niveau de la table : faible surcharge, verrouillage rapide ; pas de blocages importants, probabilité la plus élevée de conflits de verrouillage et concurrence la plus basse.
2) Verrous au niveau des lignes : des surcharges élevées et des blocages lents peuvent survenir ; la granularité du verrouillage est la plus faible, la probabilité de conflits de verrouillage est la plus faible et la concurrence est également la plus élevée.
3) Verrouillage de page : le coût et le temps de verrouillage se situent entre les verrous de table et les verrous de ligne ; la granularité du verrouillage se situe entre les verrous de table et les verrous de ligne, et la concurrence est moyenne.

Ce dont nous parlons principalement ici, ce sont les verrous au niveau des lignes. Généralement, dans le système de vente flash, nous utiliserons des verrous au niveau des lignes pour l'inventaire des produits, car l'inventaire est une donnée très importante lors de la vente flash. Nous créons une table de base de données. Les paramètres suivants peuvent apparaître :

ENGINE = InnoDB AUTO_INCREMENT=10 DEFAULT CHARACTER SET = utf8 comment='用户表

Régler le moteur sur InnoDB InnnoDB est différent des autres moteurs : premièrement, il prend en charge les transactions (TRANCSACTION), et deuxièmement, il utilise des lignes. verrous de niveau.

InnoDB中两种模式的行级锁：

1）共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。 
( Select * from table_name where ……lock in share mode) 
2）排他锁：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和 排他写锁。(select * from table_name where…..for update)

为了允许行锁和表锁共存，实现多粒度锁机制；同时还有两种内部使用的意向锁（都是表锁），分别为意向共享锁和意向排他锁。

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

注意：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

行级锁的优缺点

行级锁定的优点：

• 当在许多线程中访问不同的行时只存在少量锁定冲突。

• 回滚时只有少量的更改。

• 可以长时间锁定单一的行。

行级锁定的缺点：

• 比页级或表级锁定占用更多的内存。

• 当在表的大部分数据上使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。如果你在大部分数据上经常进行GROUP BY操作或

者必须经常扫描整个表，比其它锁定明显慢很多。

hibernate中通过行级锁实现的悲观锁。

一些例子：

假设有个表单products ，里面有id跟name二个栏位，id是主键。 
1: 明确指定主键，并且有此条记录，执行row lock。若查无此记录，无lock。

SELECT * FROM products WHERE id='3' FOR UPDATE;SELECT * FROM products WHERE id='3' and name="cat" FOR UPDATE;

2: 无主键，执行table lock。

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

3: 主键不明确，table lock。

SELECT * FROM products WHERE id<>&#39;3&#39; FOR UPDATE;

注意： FOR UPDATE仅适用于InnoDB，且必须在事务块(BEGIN/COMMIT)中才能生效。此外，如果A与B都对表id进行查询但查询不到记录，则A与B在查询上不会进行row锁，但A与B都会获取排它锁，此时A再插入一条记录的话则会因为B已经有锁而处于等待中，此时B再插入一条同样的数据则会抛出Deadlock found when trying to get lock; try restarting transaction。然后释放锁，此时A就获得了锁而插入成功。

以上就是MySQL中的事务与锁的内容，更多相关内容请关注PHP中文网（www.php.cn）！