1. <legend id='ct2pT'><style id='ct2pT'><dir id='ct2pT'><q id='ct2pT'></q></dir></style></legend>
  2. <i id='ct2pT'><tr id='ct2pT'><dt id='ct2pT'><q id='ct2pT'><span id='ct2pT'><b id='ct2pT'><form id='ct2pT'><ins id='ct2pT'></ins><ul id='ct2pT'></ul><sub id='ct2pT'></sub></form><legend id='ct2pT'></legend><bdo id='ct2pT'><pre id='ct2pT'><center id='ct2pT'></center></pre></bdo></b><th id='ct2pT'></th></span></q></dt></tr></i><div id='ct2pT'><tfoot id='ct2pT'></tfoot><dl id='ct2pT'><fieldset id='ct2pT'></fieldset></dl></div>

    <small id='ct2pT'></small><noframes id='ct2pT'>

      <bdo id='ct2pT'></bdo><ul id='ct2pT'></ul>
    <tfoot id='ct2pT'></tfoot>

    1. 如何改进 INSERT INTO ... SELECT 锁定行为

      How to improve INSERT INTO ... SELECT locking behavior(如何改进 INSERT INTO ... SELECT 锁定行为)

      <small id='s9Y6S'></small><noframes id='s9Y6S'>

    2. <i id='s9Y6S'><tr id='s9Y6S'><dt id='s9Y6S'><q id='s9Y6S'><span id='s9Y6S'><b id='s9Y6S'><form id='s9Y6S'><ins id='s9Y6S'></ins><ul id='s9Y6S'></ul><sub id='s9Y6S'></sub></form><legend id='s9Y6S'></legend><bdo id='s9Y6S'><pre id='s9Y6S'><center id='s9Y6S'></center></pre></bdo></b><th id='s9Y6S'></th></span></q></dt></tr></i><div id='s9Y6S'><tfoot id='s9Y6S'></tfoot><dl id='s9Y6S'><fieldset id='s9Y6S'></fieldset></dl></div>

          <bdo id='s9Y6S'></bdo><ul id='s9Y6S'></ul>

                <tbody id='s9Y6S'></tbody>
                <legend id='s9Y6S'><style id='s9Y6S'><dir id='s9Y6S'><q id='s9Y6S'></q></dir></style></legend>

                <tfoot id='s9Y6S'></tfoot>
                本文介绍了如何改进 INSERT INTO ... SELECT 锁定行为的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                在我们的生产数据库中,我们每小时运行以下伪代码 SQL 批处理查询:

                In our production database, we ran the following pseudo-code SQL batch query running every hour:

                INSERT INTO TemporaryTable
                    (SELECT FROM HighlyContentiousTableInInnoDb
                     WHERE allKindsOfComplexConditions are true)
                

                现在这个查询本身不需要很快,但我注意到它正在锁定 HighlyContentiousTableInInnoDb,即使它只是从中读取.这使得其他一些非常简单的查询需要大约 25 秒(这是其他查询需要的时间).

                Now this query itself does not need to be fast, but I noticed it was locking up HighlyContentiousTableInInnoDb, even though it was just reading from it. Which was making some other very simple queries take ~25 seconds (that's how long that other query takes).

                然后我发现这种情况下的 InnoDB 表实际上是被 SELECT 锁定的!https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

                Then I discovered that InnoDB tables in such a case are actually locked by a SELECT! https://www.percona.com/blog/2006/07/12/insert-into-select-performance-with-innodb-tables/

                但我不太喜欢选择进入 OUTFILE 的文章中的解决方案,这似乎是一个 hack(文件系统上的临时文件似乎很糟糕).还有其他想法吗?有没有办法制作 InnoDB 表的完整副本,而无需在复制期间以这种方式锁定它.然后我可以将 HighlyContentiousTable 复制到另一个表并在那里进行查询.

                But I don't really like the solution in the article of selecting into an OUTFILE, it seems like a hack (temporary files on filesystem seem sucky). Any other ideas? Is there a way to make a full copy of an InnoDB table without locking it in this way during the copy. Then I could just copy the HighlyContentiousTable to another table and do the query there.

                推荐答案

                这个问题的答案现在容易多了:- 使用基于行的复制和读提交隔离级别.

                The answer to this question is much easier now: - Use Row Based Replication and Read Committed isolation level.

                您遇到的锁定消失了.

                更长的解释:http://harrison-fisk.blogspot.com/2009/02/my-favorite-new-feature-of-mysql-51.html

                这篇关于如何改进 INSERT INTO ... SELECT 锁定行为的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                SQL Server Bulk insert of CSV file with inconsistent quotes(SQL Server 批量插入带有不一致引号的 CSV 文件)
                How to update a record using sequelize for node?(如何使用节点的 sequelize 更新记录?)
                How to provide a mysql database connection in single file in nodejs(如何在 nodejs 中的单个文件中提供 mysql 数据库连接)
                Looping Over Result Sets in MySQL(在 MySQL 中循环结果集)
                SQL - Inserting a row and returning primary key(SQL - 插入一行并返回主键)
                Python and SQLite: insert into table(Python 和 SQLite:插入表)
                <legend id='1L7PI'><style id='1L7PI'><dir id='1L7PI'><q id='1L7PI'></q></dir></style></legend>
                    <bdo id='1L7PI'></bdo><ul id='1L7PI'></ul>
                    <tfoot id='1L7PI'></tfoot>

                    1. <i id='1L7PI'><tr id='1L7PI'><dt id='1L7PI'><q id='1L7PI'><span id='1L7PI'><b id='1L7PI'><form id='1L7PI'><ins id='1L7PI'></ins><ul id='1L7PI'></ul><sub id='1L7PI'></sub></form><legend id='1L7PI'></legend><bdo id='1L7PI'><pre id='1L7PI'><center id='1L7PI'></center></pre></bdo></b><th id='1L7PI'></th></span></q></dt></tr></i><div id='1L7PI'><tfoot id='1L7PI'></tfoot><dl id='1L7PI'><fieldset id='1L7PI'></fieldset></dl></div>
                          <tbody id='1L7PI'></tbody>
                      • <small id='1L7PI'></small><noframes id='1L7PI'>