Y a-t-il un moyen de sélectionner sans provoquer de verrouillage dans MySQL ?

Question

Une requête :

SELECT COUNT(online.account_id) cnt from online;

Mais la table en ligne est également modifiée par un événement, de sorte que je peux fréquemment voir le verrouillage en exécutant show processlist .

Existe-t-il une grammaire dans MySQL qui peut faire en sorte que l'instruction select ne provoque pas de verrouillages ?

Et j'ai oublié de mentionner plus haut que c'est sur une base de données MySQL esclave.

Après avoir ajouté dans my.cnf:transaction-isolation = READ-UNCOMMITTED l'esclave rencontrera l'erreur :

Erreur "L'enregistrement binaire est impossible. Message : Le niveau de transaction 'READ-UNCOMMITTED' dans InnoDB n'est pas sûr pour le mode d'enregistrement binaire 'STATEMENT'' sur une requête.

Alors, existe-t-il un moyen compatible de faire cela ?

Answer 1

J'ai trouvé un article intitulé "MYSQL AVEC NOLOCK".

https://web.archive.org/web/20100814144042/http://sqldba.org/articles/22-mysql-with-nolock.aspx

dans MS SQL Server, vous devez faire ce qui suit :

SELECT * FROM TABLE_NAME WITH (nolock)

et l'équivalent MYSQL est

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ ;

EDIT

Michael Mior a suggéré ce qui suit (tiré des commentaires)

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT * FROM TABLE_NAME ;
COMMIT ;

Answer 2

Si la table est InnoDB, voir http://dev.mysql.com/doc/refman/5.1/en/innodb-consistent-read.html -- il utilise la lecture cohérente (mode sans verrouillage) pour les SELECT "qui ne spécifient pas FOR UPDATE ou LOCK IN SHARE MODE si l'option innodb_locks_unsafe_for_binlog est définie et si le niveau d'isolation de la transaction n'est pas défini sur SERIALIZABLE. Ainsi, aucun verrou n'est posé sur les lignes lues à partir de la table sélectionnée".

Answer 3

Utilisez

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

Les documents de la version 5.0 sont ici .

Les documents de la version 5.1 sont ici .

Answer 4

Vous pouvez lire cette page du manuel MySQL. La façon dont une table est verrouillée dépend du type de table dont il s'agit.

MyISAM utilise des verrous de table pour atteindre une vitesse de lecture très élevée, mais si vous avez une instruction UPDATE en attente, les futurs SELECTS feront la queue derrière l'UPDATE.

Les tables InnoDB utilisent le verrouillage au niveau de la ligne, et vous ne verrez pas la table entière se verrouiller derrière un UPDATE. Il y a d'autres types de problèmes de verrouillage associés à InnoDB, mais vous pourriez trouver que cela correspond à vos besoins.

Answer 5

Selon le type de votre table, le verrouillage fonctionnera différemment, mais il en sera de même pour un comptage SELECT. Pour les tables MyISAM, un simple SELECT count(*) FROM table ne devrait pas verrouiller la table puisqu'il accède aux métadonnées pour obtenir le nombre d'enregistrements. Innodb prendra plus de temps puisqu'il doit saisir la table dans un instantané pour compter les enregistrements, mais cela ne devrait pas causer de verrouillage.

Vous devriez au moins avoir la valeur 1 (par défaut) pour concurrent_insert. Ensuite, s'il n'y a pas de "trous" dans le fichier de données à remplir par la table, les insertions seront ajoutées au fichier et les SELECT et INSERT peuvent se produire simultanément avec les tables MyISAM. Notez que la suppression d'un enregistrement crée un "vide" dans le fichier de données qui tentera d'être comblé par de futures insertions et mises à jour.

Si vous supprimez rarement des enregistrements, vous pouvez définir concurrent_insert égal à 2, et les insertions seront toujours ajoutées à la fin du fichier de données. Les sélections et les insertions peuvent alors se produire simultanément, mais votre fichier de données ne sera jamais plus petit, quel que soit le nombre d'enregistrements que vous supprimez (sauf tous les enregistrements).

En résumé, si vous avez beaucoup de mises à jour, d'insertions et de sélections sur une table, vous devez la rendre InnoDB. Vous pouvez cependant mélanger librement les types de tables dans un système.