假人指南锁定innodb

问题描述

有关锁定innodb的典型文档过于混乱.我认为拥有"innodb锁定的虚拟指南"将具有很大的价值.

The typical documentation on locking in innodb is way too confusing. I think it will be of great value to have a "dummies guide to innodb locking"

我将开始，并将所有响应收集为Wiki:

I will start, and I will gather all responses as a wiki:

• 在应用行级别锁定之前，需要对列进行索引.
  • 示例:删除其中column1 = 10的行；除非列1已被索引，否则将锁定表
  • The column needs to be indexed before row level locking applies.
    • EXAMPLE: delete row where column1=10; will lock up the table unless column1 is indexed

    推荐答案

    以下是我与MySQL支持一起处理一个最近发生的奇怪的锁定问题(版本5.1.37)的笔记:

    Here are my notes from working with MySQL support on a recent, strange locking issue (version 5.1.37):

    遍历所有要更改的行和索引条目将被锁定.涵盖在:

    All rows and index entries traversed to get to the rows being changed will be locked. It's covered at:

    http://dev.mysql.com /doc/refman/5.1/en/innodb-locks-set.html

    锁定读取，UPDATE或DELETE通常会对在处理SQL语句时扫描的每个索引记录设置记录锁定.在语句中是否存在将排除该行的条件并不重要.InnoDB不记得确切的WHERE条件，而是只知道扫描了哪个索引范围....如果您没有适合您的语句的索引，并且MySQL必须扫描整个表以处理该语句，则表的每一行都将被锁定，这又阻止了其他用户对表的所有插入."

    "A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned. ... If you have no indexes suitable for your statement and MySQL must scan the entire table to process the statement, every row of the table becomes locked, which in turn blocks all inserts by other users to the table."

    是的.通常有用的解决方法是:

    It is. A workaround that is often helpful is to do:

    更新whichevertable将任何内容设置为主键所在的位置(从约束条件中按主键选择主键中的主键)；

    UPDATE whichevertable set whatever to something where primarykey in (select primarykey from whichevertable where constraints order by primarykey);

    内部选择不需要锁，因此更新将需要较少的工作来进行更新. order by子句可确保以主键顺序完成更新，以匹配InnoDB的物理顺序，这是最快的方式.

    The inner select doesn't need to take locks and the update will then have less work to do for the updating. The order by clause ensures that the update is done in primary key order to match InnoDB's physical order, the fastest way to do it.

    在涉及大量行的情况下(如您的情况)，最好将选择结果存储在添加了flag列的临时表中.然后从未设置标志的临时表中选择以获取每个批次.运行限制为例如1000或10000的更新，并在更新后为批处理设置标志.限制将使锁定量保持在可容忍的水平，而选择工作仅需要执行一次.每次批处理后提交以解除锁定.

    Where large numbers of rows are involved, as in your case, it can be better to store the select result in a temporary table with a flag column added. Then select from the temporary table where the flag is not set to get each batch. Run updates with a limit of say 1000 or 10000 and set the flag for the batch after the update. The limits will keep the amount of locking to a tolerable level while the select work will only have to be done once. Commit after each batch to release the locks.

    您还可以通过在执行每一批更新之前对未索引的列进行选择总和来加快此过程.这会将数据页加载到缓冲池中，而不会进行锁定.然后锁定将持续较短的时间，因为不会读取任何磁盘.

    You can also speed this work up by doing a select sum of an unindexed column before doing each batch of updates. This will load the data pages into the buffer pool without taking locks. Then the locking will last for a shorter timespan because there won't be any disk reads.

    这并不总是可行的，但是在可行的时候会很有帮助.如果您无法批量处理，则至少可以尝试使用select来预加载数据(如果它足够小以适合缓冲池).

    This isn't always practical but when it is it can be very helpful. If you can't do it in batches you can at least try the select first to preload the data, if it's small enough to fit into the buffer pool.

    如果可能，请使用READ COMMITTED事务隔离模式.参见:

    If possible use the READ COMMITTED transaction isolation mode. See:

    http://dev.mysql.com/doc /refman/5.1/en/set-transaction.html

    要获得减少的锁定，需要使用基于行的二进制日志记录(而不是基于默认语句的二进制日志记录).

    To get that reduced locking requires use of row-based binary logging (rather than the default statement based binary logging).

    两个已知问题:

    1. 子查询有时可能无法理想地优化.在这种情况下，这是一个不希望的依赖子查询-因此，我提出的使用子查询的建议与这种情况下的替代方法相比是无济于事的.

    1. Subqueries can be less than ideally optimised sometimes. In this case it was an undesirable dependent subquery - the suggestion I made to use a subquery turned out to be unhelpful compared to the alternative in this case because of that.

    删除和更新的查询计划范围与select语句不同，因此有时在不测量结果以准确确定其作用的情况下，很难对其进行适当的优化.

    Deletes and updates do not have the same range of query plans as select statements so sometimes it's hard to properly optimise them without measuring the results to work out exactly what they are doing.

    这两者都在逐步改善.该错误是一个示例，其中我们刚刚改进了更新的可用优化，尽管更改非常重要，并且仍在进行质量检查以确保不会产生重大不利影响:

    Both of these are gradually improving. This bug is one example where we've just improved the optimisations available for an update, though the changes are significant and it's still going through QA to be sure it doesn't have any great adverse effects:

    http://bugs.mysql.com/bug.php?id=36569

    这篇关于假人指南锁定innodb的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！