Comment améliorer L'insertion dans ... sélectionner le comportement de verrouillage

Vous pouvez définir binlog format comme ça:

SET GLOBAL binlog_format = 'ROW';

Modifier mon.cnf si vous voulez faire si elle est permanente:

[mysqld]
binlog_format=ROW

Définir le niveau d'isolation pour la session en cours avant d'exécuter votre requête:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
INSERT INTO t1 SELECT ....;

si cela ne vous aide pas, vous devriez essayer de configurer le niveau d'isolation du serveur à l'échelle et pas seulement pour la session en cours:

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Modifier mon.cnf si vous voulez faire si elle est permanente:

[mysqld]
transaction-isolation = READ-UNCOMMITTED

vous pouvez modifier READ-UNCOMMITTED à READ-COMMITTED qui est un meilleur niveau d'isolement.

tout le monde utilisant des tables Innodb s'est probablement habitué au fait Innodb les tables effectuent des lectures sans verrouillage, c'est-à-dire à moins que vous n'utilisiez certains modificateurs tels que verrouillage en mode Partage ou pour mise à jour, sélectionnez déclarations ne verrouillera aucune ligne pendant la course.

Ceci est généralement correct, mais il y a une exception notable – insérer dans le Tableau1 sélectionner * du tableau2. Cette déclaration effectuera la lecture de verrouillage (serrures partagées) pour la table table2. Elle s'applique également aux tableaux similaire avec la clause where et des jointures. Il est important pour les tables qui est lu D'être Innodb-même si les Écritures sont faites dans la table MyISAM.

alors pourquoi cela a-t-il été fait, étant assez mauvais pour la performance MySQL et la simultanéité ?

la raison est la réplication. Dans MySQL avant 5.1, la réplication est basée sur une instruction, ce qui signifie que les instructions répondues sur le maître devraient causer le même effet que sur l'esclave. Si Innodb ne verrouillerait pas les lignes dans la table source autre transaction pourrait modifier la ligne et s'engager avant la transaction qui est en cours D'exécution insérer .. SELECT statement. Cela ferait en sorte que cette transaction soit appliquée sur l'esclave avant D'Insérer... sélectionnez l'énoncé et peut-être entraîner des données différentes que sur le maître. Verrouiller les lignes dans la table source tout en les lisant protège de cet effet que d'autres transactions modifie les lignes avant D'insérer ... SELECT a eu la chance d'y accéder il sera également modifié dans le même ordre sur l'esclave. Si la transaction tente de modifier la ligne après qu'elle ait été consultée et ainsi verrouillée par INSERT ... SELECT, transaction devra attendre jusqu'à ce que la déclaration soit terminée pour s'assurer qu'elle sera exécutée sur l'esclave dans le bon ordre. Devient assez compliqué ? Eh bien, tout ce que vous devez savoir, il a dû être fait avant la réplication pour travailler à droite dans MySQL avant 5.1.

dans MySQL 5.1 ceci ainsi que quelques autres problèmes devraient être résolus par la réplication basée sur les lignes. Mais je n'ai pas encore fait de vrais tests de résistance pour voir si elle fonctionne bien. :)

encore une chose à garder en compte – INSERT ... SELECT effectue en fait la lecture en mode de verrouillage et contourne donc partiellement le versioning et récupère la dernière ligne engagée. Donc, même si vous êtes en mode répétable-lecture, cette opération sera effectuée en lecture engagée mode, donnant potentiellement un résultat différent par rapport à ce que pur SELECT donnerait. Par la façon s'applique à SÉLECTIONNER .. Verrouillez le mode de partage et sélectionnez ... pour mise à jour.

On ma demander ce qui est si je n'utilise pas la réplication et que mon log binaire est désactivé ? Si la réplication n'est pas utilisée, Vous pouvez activer l'option innodb_locks_unsafe_for_binlog, qui détendra les serrures que Innodb fixe sur l'exécution des instructions, ce qui donne généralement une meilleure simultanéité. Cependant, comme le nom dit qu'il rend les serrures dangereuses pour la réplication et le point dans la récupération de temps, donc utiliser innodb_locks_unsafe_for_binlog option avec prudence.

Note désactiver les logs binaires n'est pas suffisant pour déclencher des serrures relâchées. Vous il faut aussi définir innodb_locks_unsafe_for_binlog=1. Ceci est fait de sorte l'activation du journal binaire ne provoque pas de changements inattendus dans le verrouillage problèmes de comportement et de performance. Vous pouvez également utiliser cette option avec la réplication parfois, si vous savez vraiment ce que vous faites. Je voudrais recommande pas, sauf si il est vraiment nécessaire que vous pourriez ne pas savoir quelles autres serrures seront relâchées dans les versions futures et comment il serait affecter votre réplication.

vous pouvez probablement utiliser la commande Create View (voir Créer Une Vue De La Syntaxe). Par exemple,

Create View temp as SELECT FROM HighlyContentiousTableInInnoDb WHERE allKindsOfComplexConditions are true

après cela vous pouvez utiliser votre insert statement avec cette vue. Quelque chose comme ceci

INSERT INTO TemporaryTable (SELECT * FROM temp)

Ce n'est que ma proposition.

avertissement: je n'ai pas beaucoup d'expérience avec les bases de données, et je ne suis pas sûr que cette idée soit réalisable. S'il vous plaît, corrigez-moi si ce n'est pas le cas.

Que Diriez-vous de mettre en place une table d'équivalence secondaire HighlyContentiousTableInInnoDb2, et la création d' AFTER INSERT etc. déclencheurs dans le premier tableau qui maintiennent le nouveau tableau à jour avec les mêmes données. Maintenant vous devriez être capable de verrouiller HighlyContentiousTableInInnoDb2, et seulement ralentir les déclencheurs de la table primaire, au lieu de toutes les requêtes.

Potentiel problèmes:

• 2 x données stockées
• travaux supplémentaires pour toutes les insertions, mises à jour et suppressions
• Peut-être pas un point de vue transactionnel de son

si vous pouvez autoriser certaines anomalies, vous pouvez changer le niveau D'ISOLATION au niveau le moins strict - lire non engagé. Mais pendant ce temps, quelqu'un est autorisé à lire à partir de notre table de destination. Ou vous pouvez verrouiller la table de destination manuellement (je suppose que mysql donne cette fonctionnalité?).

ou alternativement vous pouvez utiliser READ COMMITTED, qui ne devrait pas verrouiller la table source aussi. Mais il bloque aussi les lignes insérées dans la table de destination jusqu'à commit.

je choisirais la seconde un.

Je ne suis pas familier avec MySQL, mais j'espère qu'il y a un équivalent aux niveaux d'isolation des transactions Snapshot et Read committed snapshot dans SQL Server. L'utilisation de ces devrait résoudre votre problème.

la raison du verrouillage (readlock) est de sécuriser votre transaction de lecture pour ne pas lire des données "sales" qu'une transaction parallèle pourrait être en train d'écrire. La plupart des SGBD offrent le paramètre que les utilisateurs peuvent régler et révoquer les serrures en lecture et écriture manuellement. Cela pourrait être intéressant pour vous si la lecture de données incorrectes n'est pas un problème dans votre cas.

je pense qu'il n'y a pas de façon sûre de lire à partir d'une table sans serrures dans un DBS avec des transactions multiples.

Mais ce qui suit est une séance de brainstorming: si l'espace n'est pas un problème, vous pouvez penser à exécuter deux instances de la même table. HighlyContentiousTableInInnoDb2 pour votre transaction de lecture / écriture en permanence et un HighlyContentiousTableInInnoDb2_shadow pour votre accès groupé. Peut-être Pouvez-vous remplir la table d'ombres automatisée via la gâchette/routines à l'intérieur de votre SGBD, ce qui est plus rapide et plus intelligent qu'une transaction d'écriture supplémentaire partout.

une autre idée est la question: Est-ce que toutes les transactions doivent accéder à la table entière? Sinon, vous pouvez utiliser des vues pour verrouiller uniquement colonnes nécessaires. Si l'accès continu et votre accès groupé sont disjoints en ce qui concerne les colonnes, il est possible qu'ils ne se verrouillent pas l'un l'autre!

j'ai été confronté au même problème à l'aide de CREATE TEMPORARY TABLE ... SELECT ...SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction.

basé sur votre requête initiale, mon problème a été résolu en verrouillant le HighlyContentiousTableInInnoDb avant de lancer la requête.

LOCK TABLES HighlyContentiousTableInInnoDb READ;
INSERT INTO TemporaryTable
    (SELECT FROM HighlyContentiousTableInInnoDb
    WHERE allKindsOfComplexConditions are true)
UNLOCK TABLES;