blocks|key|2957477|text|如果使用读提交，InnoDB将避免使用某些类型的锁。这可以帮助您避免死锁。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2957478|我设计了一个完整的演示文稿：用棍子图解释InnoDB锁定。|offset|length|2957479|但实际上你永远也无法避免100%25的死锁。它们不是一个错误，它们是并发系统的自然组成部分。您可以减少死锁发生的频率，但您最好还是习惯于获取一些死锁。设计代码以捕获异常，并在出现死锁时重试数据库操作。|2957480|entityMap|0|LINK|mutability|MUTABLE|url|https://www.slideshare.net/billkarwin/innodb-locking-explained-with-stick-figures^0|0|E|E|0|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@$D|R|E|S|1|T]]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

InnoDB avoids using certain kinds of locks if you use read-committed. This can help you avoid a deadlock.

I designed a whole presentation about this: <a href="https://www.slideshare.net/billkarwin/innodb-locking-explained-with-stick-figures" rel="noreferrer">InnoDB Locking Explained with Stick Figures</a>.

But you'll practically never be able to avoid 100% cases of deadlocks. They're not a mistake, they're a natural part of concurrent systems. You can reduce how frequently deadlocks occur, but you might as well get used to getting some. Design your code to catch exceptions and retry database operations when they get a deadlock.

blocks|key|1627220|text|原因是，如果表中不存在记录，那么当SELECT+...+FOR+UPDATE隔离模式处于活动状态时，它看起来就像在共享模式下(或类似的东西--在任何地方都没有文档记录)获取索引记录的下键锁。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1627221|让我们尝试一个在t隔离模式下为空的简单表REPEATABLE+READ的示例。|1627222|t1>+SELECT+*+FROM+t+WHERE+id+=+1+FOR+UPDATE;
++++no+rows+found,+next-key+lock+acquired+in+shared+mode
t2>+SELECT+*+FROM+t+WHERE+id+=+1+FOR+UPDATE;
++++no+rows+found,+next-key+lock+acquired+in+shared+mode
t1>+INSERT+INTO+t+(id)+VALUES+(1);
++++transaction+t1+is+blocked+by+t2
t2>+INSERT+INTO+t+(id)+VALUES+(1);
++++transaction+t2+is+blocked+by+t1+-+deadlock|code-block|syntax|javascript|1627223|即使第二个SELECT和INSERT将使用id=2，也会发生死锁，因为它也落入了由SELECT+...+FOR+UPDATE锁定的在t1中执行的相同空白中。如果表是空的，这个间隙就是无穷大。如果表不为空，则插入不同记录的死锁概率较小，但仍然很大(这取决于表中有多少间隙，以及插入到表尾的频率--最大的差距)。|BOLD|1627224|之所以会发生这种情况，是因为当记录不存在时，来自t1和t2+的不会互相阻塞。对于现有的记录，它在t1中获取记录上的X(独占)锁，因此t2将被阻塞，直到t1被提交或回滚。但是，如果记录不存在--它获取空白上的S(共享)下一个键锁(我不确定它是否真的是S锁(在任何地方都没有记录)，但是MySQL如何允许在相同的间隙上同时获得2个锁？)。这是死锁的主要原因--+t1和t2都试图获取间隙上的IX+(插入意图)锁，然后在插入的记录上获得X锁，但都因为SELECT+...+FOR+UPDATE获得的锁而彼此等待。|1627225|当使用READ+COMMITED事务隔离级别时，不存在此问题。如果没有找到记录，并且使用了SELECT+...+FOR+UPDATE隔离级别，则READ+COMMITED不会持有任何锁。所以第一个INSERT将会成功。第二个INSERT将被第一个INSERT获得的独占锁所阻塞，而在t1被提交之后，第二个INSERT只会抛出Duplicate+entry+'1'+for+key+'PRIMARY'。|1627226|你现在可以认为现在的情况并不是更好的僵局。只是又一个错误。但是现在假设第二个INSERT试图用id=2插入一条记录。在这种情况下，它不会被t1阻止，这两个事务都会成功。对于某些应用程序来说，这是一个大差异。|1627227|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_next_key_lock^0|H|L|2J|3|0|0|8|1|K|F|0|0|L|4|15|L|1U|2|2J|3|0|O|2|R|2|1C|2|1U|2|23|2|4Z|2|52|2|66|L|U|1|0|3|D|19|L|20|D|2Q|6|34|6|3F|6|3X|2|48|6|4I|11|0|12|6|1B|4|1X|2|2R|3|0^^$0|@$1|2|3|4|5|6|7|13|8|@$9|14|A|15|B|C]]|D|@$9|16|A|17|1|18]]|E|$]]|$1|F|3|G|5|6|7|19|8|@$9|1A|A|1B|B|C]|$9|1C|A|1D|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|1E|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1F|8|@$9|1G|A|1H|B|C]|$9|1I|A|1J|B|C]|$9|1K|A|1L|B|C]|$9|1M|A|1N|B|O]]|D|@]|E|$]]|$1|P|3|Q|5|6|7|1O|8|@$9|1P|A|1Q|B|C]|$9|1R|A|1S|B|C]|$9|1T|A|1U|B|C]|$9|1V|A|1W|B|C]|$9|1X|A|1Y|B|C]|$9|1Z|A|20|B|C]|$9|21|A|22|B|C]|$9|23|A|24|B|C]|$9|25|A|26|B|O]]|D|@]|E|$]]|$1|R|3|S|5|6|7|27|8|@$9|28|A|29|B|C]|$9|2A|A|2B|B|C]|$9|2C|A|2D|B|C]|$9|2E|A|2F|B|C]|$9|2G|A|2H|B|C]|$9|2I|A|2J|B|C]|$9|2K|A|2L|B|C]|$9|2M|A|2N|B|C]|$9|2O|A|2P|B|C]]|D|@]|E|$]]|$1|T|3|U|5|6|7|2Q|8|@$9|2R|A|2S|B|C]|$9|2T|A|2U|B|C]|$9|2V|A|2W|B|C]|$9|2X|A|2Y|B|O]]|D|@]|E|$]]|$1|V|3|-4|5|6|7|2Z|8|@]|D|@]|E|$]]]|W|$X|$5|Y|Z|10|E|$11|12]]]]

The reason is that if record doesn't exist in the table it looks like <code>SELECT ... FOR UPDATE</code> acquires <a href="https://dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_next_key_lock" rel="nofollow noreferrer">next-key lock</a> in shared mode (or something similar - it's not documented anywhere) for an index record following a non-existent record when <code>REPEATABLE READ</code> isolation mode is active.
Let's try an example for a simple table <code>t</code> that is empty in <code>REPEATABLE READ</code> isolation mode.
<pre class="lang-sql prettyprint-override"><code>t1&gt; SELECT * FROM t WHERE id = 1 FOR UPDATE;
 no rows found, next-key lock acquired in shared mode
t2&gt; SELECT * FROM t WHERE id = 1 FOR UPDATE;
 no rows found, next-key lock acquired in shared mode
t1&gt; INSERT INTO t (id) VALUES (1);
 transaction t1 is blocked by t2
t2&gt; INSERT INTO t (id) VALUES (1);
 transaction t2 is blocked by t1 - deadlock
</code></pre>
The deadlock will happen even if the second SELECT and INSERT will use <code>id=2</code> because it also falls into the same gap, locked by <code>SELECT ... FOR UPDATE</code>, executed in <code>t1</code>. And if the table is empty this gap is infinity. If the table is not empty the probability of deadlock is smaller for inserting different records, but is still big (it depends on how many gaps in the table and how often you insert to the end of the table - the largest gap).
This happens because <code>SELECT ... FOR UPDATE</code> from <code>t1</code> and <code>t2</code> doesn't block each other when record doesn't exist. For existent record it acquires the X (exclusive) lock on the record in <code>t1</code>, so <code>t2</code> will be blocked until <code>t1</code> is committed or rolled back. But if the record doesn't exist - it acquires the S (shared) next-key lock on the gap (I'm not sure that it is really an S lock (it is not documented anywhere), but how else MySQL allows to acquire 2 locks concurrently on the same gap?). And this is the main reason of deadlock here - both <code>t1</code> and <code>t2</code> trying to acquire the IX (insert intention) lock on the gap and then X lock on the inserted record, but both waiting each other because of the lock, acquired by <code>SELECT ... FOR UPDATE</code>.
This problem doesn't exist when <code>READ COMMITED</code> transaction isolation level is used. <code>SELECT ... FOR UPDATE</code> doesn't hold any locks if record is not found and <code>READ COMMITED</code> isolation level is used. So the first <code>INSERT</code> will succeed. The second <code>INSERT</code> will be blocked by EXCLUSIVE lock acquired by first <code>INSERT</code> and after <code>t1</code> will be commited, the second <code>INSERT</code> will just throw <code>Duplicate entry '1' for key 'PRIMARY'</code>.
You could think now that this situation is not better that deadlock. Just another error. But now imagine that second <code>INSERT</code> tries to insert a record with <code>id=2</code>. In this case it will not be blocked by <code>t1</code> and both transactions will succeed. And this is a big difference for some applications.

In the Mysql Document: "<a href="https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html" rel="noreferrer">https://dev.mysql.com/doc/refman/5.7/en/innodb-deadlocks-handling.html</a>"

It mentioned: "If you use locking reads (SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE), try using a lower isolation level such as READ COMMITTED."

Could someone tell me why I can't use "Repeatable Read"? Example will be good.

Cheers

Why I shouldn't use "Repeatable Read" with locking reading (select..for update)"?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

在Mysql文档中："“它提到：“如果使用锁读(选择.用于更新或选择.锁定共享模式)，尝试使用较低的隔离级别，如ReadCommit.”有人能告诉我为什么我不能用“可重复阅读”吗？以身作则会很好。干杯

问为什么我不应该使用“可重复读取”与锁定读取(select..for更新)“？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问为什么我不应该使用“可重复读取”与锁定读取(select..for更新)“？EN