blocks|key|1421216|text|由于两个窗口都是重叠的，我认为可能存在问题。Oracle为两个查询锁定相同的块，因此在同时执行时会出现问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1421217|另外，检查SO上的answer+in+this+question。|offset|length|1421218|通过检查dba_lock表来检查是否发生了锁。请参见文档here。|style|CODE|1421219|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/11691537/is-a-deadlock-possible-when-updating-and-deleting-different-rows-in-a-table|1|http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_4111.htm#REFRN23116^0|0|9|N|0|0|4|8|S|4|1|0^^$0|@$1|2|3|4|5|6|7|T|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|U|8|@]|9|@$D|V|E|W|1|X]]|A|$]]|$1|F|3|G|5|6|7|Y|8|@$D|Z|E|10|H|I]]|9|@$D|11|E|12|1|13]]|A|$]]|$1|J|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|K|$L|$5|M|N|O|A|$P|Q]]|R|$5|M|N|O|A|$P|S]]]]

Since both the windows are overlapping, I think there might be the issue. Oracle locks the same blocks for the two queries, thus giving problems when executing at the same time.

Also, check the <a href="https://stackoverflow.com/questions/11691537/is-a-deadlock-possible-when-updating-and-deleting-different-rows-in-a-table">answer in this question</a> on SO.

Check on there the lock occurs by checking the <code>dba_lock</code> table. See docs <a href="http://docs.oracle.com/cd/E11882_01/server.112/e40402/statviews_4111.htm#REFRN23116" rel="nofollow noreferrer">here</a>.

blocks|key|1421320|text|是的，您所呈现的那些语句可能会产生死锁。最简单的场景之一是：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1421321|会话1发出第一条statement;|1421322|Session+statement;|unordered-list-item|1421323|
|1421324|Session+2发出第二条update+Session+1发出第二条update语句；|offset|length|style|CODE|1421325|1421326|示例：|1421327|--+Session+#1+locks+a+subset+of+rows
SQL>+update+mytable+
++++++++set+column1+=+column1+%2B+589+
++++++where+column1+>=+7988+
++++++++and+column1+<=+7988;

1+row+updated.

+++++++++++++++++++++++++++++++++++++++++++--+session+#2
+++++++++++++++++++++++++++++++++++++++++++--+trying+to+lock+subset+of+rows
+++++++++++++++++++++++++++++++++++++++++++--+locked+by+the+first+session+update
++++++++++++++++++++++++++++++++++++++++++SQL>+update+mytable+
++++++++++++++++++++++++++++++++++++++++++++++++++set+column1+=+column1+%2B+660+
++++++++++++++++++++++++++++++++++++++++++++++++where+column1+>=+7840;


--+session+#1
--+trying+to+lock+subset+of+rows+that+are+locked
--+by+session+#2+update,+that+in+turn's
--+trying+to+lock+the+subset+of+rows+
--+locked+by+session+#1+update+statement
SQL>+update+mytable+
++++++++set+column1+=+column1+%2B+660+
++++++where+column1+>=+7840;


++++++++++++++++++++++++++++++++++++++++++--+session+#2
++++++++++++++++++++++++++++++++++++++++++--+thus+deadlock
++++++++++++++++++++++++++++++++++++++++++update+mytable+
++++++++++++++++++++++++++++++++++++++++++++++set+column1+=+column1+%2B+660+
++++++++++++++++++++++++++++++++++++++++++++where+column1+>=+7840
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*
++++++++++++++++++++++++++++++++++++++++++ERROR+at+line+1:
++++++++++++++++++++++++ORA-00060:+deadlock+detected+while+waiting+for+resource|code-block|syntax|javascript|1421328|当您使用where子句构造update或delete语句时，该语句将变成由读取和写入部分组成的两部分语句。读取part会保留行for+update，而写入一行则会更改它们。因此，第一个会话的update语句锁定某些数据集以进行更新，更改它们并保持锁定，直到您发出commit或rollback，第二个会话的update语句锁定其所在行的部分，而第一个会话试图锁定被第二个会话锁定的那部分数据，从而导致死锁。如果您在一个会话中发出这些更新语句，则应该不会出现死锁。但是，当您在多个会话中同时发出这些update语句而没有commit或rollback时，请准备好ORA-00060异常。|1421329|每个死锁都伴随着警报文件的创建。检查该文件以获取有关死锁的更多详细信息。|1421330|1421331|有什么可以避免死锁的提示吗？|blockquote|1421332|1421333|1421334|不，没有这样的提示。|1421335|entityMap^0|0|0|0|0|E|6|Z|6|0|0|0|0|4|5|D|6|K|6|1R|A|2N|6|3N|6|3U|8|49|6|7S|9|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1B|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1C|8|@]|9|@]|A|$]]|$1|G|3|H|5|6|7|1D|8|@]|9|@]|A|$]]|$1|I|3|J|5|F|7|1E|8|@$K|1F|L|1G|M|N]|$K|1H|L|1I|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1K|8|@]|9|@]|A|$]]|$1|R|3|S|5|T|7|1L|8|@]|9|@]|A|$U|V]]|$1|W|3|X|5|6|7|1M|8|@$K|1N|L|1O|M|N]|$K|1P|L|1Q|M|N]|$K|1R|L|1S|M|N]|$K|1T|L|1U|M|N]|$K|1V|L|1W|M|N]|$K|1X|L|1Y|M|N]|$K|1Z|L|20|M|N]|$K|21|L|22|M|N]|$K|23|L|24|M|N]]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|25|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|26|8|@]|9|@]|A|$]]|$1|11|3|12|5|13|7|27|8|@]|9|@]|A|$]]|$1|14|3|-4|5|6|7|28|8|@]|9|@]|A|$]]|$1|15|3|-4|5|6|7|29|8|@]|9|@]|A|$]]|$1|16|3|17|5|6|7|2A|8|@]|9|@]|A|$]]|$1|18|3|-4|5|6|7|2B|8|@]|9|@]|A|$]]]|19|$]]

Yes, those statements you've presented can produce deadlock. One of the simplest scenario would be:

<ol>
<li>Session 1 issues first <code>update</code> statement;</li>
<li>Session 2 issues second <code>update</code> statement;</li>
<li>Session 1 issues second <code>update</code> statement;</li>
</ol>

Example:

<pre><code>-- Session #1 locks a subset of rows
SQL&gt; update mytable 
 set column1 = column1 + 589 
 where column1 &gt;= 7988 
 and column1 &lt;= 7988;

1 row updated.

 -- session #2
 -- trying to lock subset of rows
 -- locked by the first session update
 SQL&gt; update mytable 
 set column1 = column1 + 660 
 where column1 &gt;= 7840;


-- session #1
-- trying to lock subset of rows that are locked
-- by session #2 update, that in turn's
-- trying to lock the subset of rows 
-- locked by session #1 update statement
SQL&gt; update mytable 
 set column1 = column1 + 660 
 where column1 &gt;= 7840;


 -- session #2
 -- thus deadlock
 update mytable 
 set column1 = column1 + 660 
 where column1 &gt;= 7840
 *
 ERROR at line 1:
 ORA-00060: deadlock detected while waiting for resource
</code></pre>

When you construct an <code>update</code> or <code>delete</code> statement using <code>where</code> clause, the statement becomes a two-part statement that consist of reading and writing parts. Reading part reserves rows <code>for update</code> and writing one changes them. So first session's <code>update</code> statement locks some set of data for update, changes them and keep them locked until you issue <code>commit</code> or <code>rollback</code>, second session's <code>update</code> statement locks its part of rows, and them first session is trying to lock that part of data that's being locked by the second one, thus deadlock. If you issue those updates statement in one session, there should be not dead lock whatsoever. But when you issue those update statements in more than one session simultaneously without commit or rollback, be prepared for <code>ORA-00060</code> exception. 

Every deadlock is accompanied by the creation of an alert file. Check the file to get more detailed information about deadlock.

<blockquote>
 Is there any hint I can apply to avoid deadlock?
</blockquote>

No, there is no such hint.

I am getting deadlock while running the below queries in Oracle

<pre><code>update MYTABLE 
 set COLUMN1 = COLUMN1 + 589 
 where COLUMN1 &gt;= 7988 
 and COLUMN1 &lt;= 7988

update MYTABLE 
 set COLUMN1 = COLUMN1 + 660 
 where COLUMN1 &gt;= 7840
</code></pre>

How come I will get a deadlock when both the queries are running in 2 separate Transactions?
My point is the second query will wait until the first query finishes its Transaction when the isolation is set to <code>READCOMMITTED</code> right?

Is there any hint I can apply to avoid deadlock?

Note: <code>COLUMN1</code> is not a PK in that table, however application ensures that there are no duplicates.

Deadlock in Oracle

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

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

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

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

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

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

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

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

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

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

在Oracle中运行以下查询时出现死锁update MYTABLE set COLUMN1 = COLUMN1 + 589 where COLUMN1 >= 7988 and COLUMN1 <= 7988update MYTABLE set COLUMN1 = COLUMN1 + 660 whe...

问Oracle中的死锁
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Oracle中的死锁EN