基于我的测试,以及我对Oracle如何工作的猜测,答案是否定的.
但我想100％肯定.我正在寻找一个官方消息来源,说死锁不会以这种方式发生,或者是一个测试案例,证明死锁可以通过这种方式发生.
提出这个问题的另一种方法是：如果使用相同的访问方法,Oracle是否会以相同的顺序返回结果？ (运行之间没有数据变化.)
例如,如果查询使用全表扫描并按4/3/2/1的顺序返回行,它是否总是按该顺序返回行？如果索引范围扫描以1/2/3/4的顺序返回行,它是否总是以该顺序返回行？实际订单是什么并不重要,只是订单是确定性的.
(并行性可能会给这个问题增加一些复杂性.语句的整体顺序会因许多因素而有所不同.但对于锁定,我认为只有每个并行会话中的顺序才是重要的.再次,我的测试表明顺序是确定性的,不会导致死锁.)
UPDATE
我原来的问题有点泛泛.我最感兴趣的是,是否有可能在同一时间运行更新table_without_index在两个不同的会话中设置a = -1,并得到一个死锁？ (我问的是单个更新,而不是一系列更新.)
首先,让我演示完全相同的语句可能导致死锁.
创建表,索引和一些数据：
为简单起见,我只更新同一列.在现实世界中会有不同的列,但我不认为这会改变任何东西.
请注意,我使用pctfree 0创建表,更新的值将占用更多空间,因此将有大量的行迁移. (这是对@Tony Andrew的答案的回应,虽然我担心我的测试可能过于简单.而且,我认为我们不需要担心在更新之间插入行;只有一个更新会看到新行所以它不会导致死锁.除非新行也转移了其他一些东西.)
drop table deadlock_test purge; create table deadlock_test(a number) pctfree 0; create index deadlock_test_index on deadlock_test(a); insert into deadlock_test select 2 from dual connect by level <= 10000; insert into deadlock_test select 1 from dual connect by level <= 10000; commit;
在会话1中运行此块：
begin
while true loop
update deadlock_test set a = -99999999999999999999 where a > 0;
rollback;
end loop;
end;
/
在会话2中运行此块：
--First,influence the optimizer so it will choose an index range scan.
--This is not gaurenteed to work for every environment. You may need to
--change other settings for Oracle to choose the index over the table scan.
alter session set optimizer_index_cost_adj = 1;
begin
while true loop
update deadlock_test set a = -99999999999999999999 where a > 0;
rollback;
end loop;
end;
/
几秒钟后,其中一个会话抛出ORA-00060：在等待资源时检测到死锁.这是因为同一查询在每个会话中以不同的顺序锁定行.
排除上述情况,是否会发生死锁？
以上表明执行计划的变化可能导致死锁.
但即使执行计划保持不变,是否会出现死锁？
据我所知,如果删除optimizer_index_cost_adj或其他任何会改变计划的内容,代码将永远不会导致死锁. (我一直在运行代码一段时间,没有错误.)
我问这个问题,因为我正在研究的系统偶尔会发生这种情况.它还没有失败,但我们想知道它是否真的安全,还是我们需要在更新中添加额外的锁定？
有人可以构建一个测试用例,其中一个同时运行并使用相同计划的单个更新语句会导致死锁吗？
至于锁定,两个相同的DML语句可以相互阻塞(但不会死锁).例如：
CREATE TABLE THE_TABLE (
ID INT PRIMARY KEY
);
交易A：
INSERT INTO THE_TABLE VALUES(1);
交易B：
INSERT INTO THE_TABLE VALUES(1);
此时,事务B停止,直到事务A提交或回滚.如果A提交,则B因PRIMARY KEY违规而失败.如果A回滚,则B成功.
可以为UPDATE和DELETE构造类似的示例.
重要的一点是阻塞不依赖于执行计划 – 无论Oracle如何选择优化您的查询,您都将始终拥有相同的阻止行为.您可能需要阅读有关Automatic Locks in DML Operations的更多信息.
至于死锁,它们可以用多个语句来实现.例如：
A: INSERT INTO THE_TABLE VALUES(1); B: INSERT INTO THE_TABLE VALUES(2); A: INSERT INTO THE_TABLE VALUES(2); B: INSERT INTO THE_TABLE VALUES(1); -- sql Error: ORA-00060: deadlock detected while waiting for resource
或者,可能使用不同顺序修改多行的语句和一些非常不幸的时间(任何人都可以确认这一点吗？).
—更新—
为了回应您的问题的更新,让我做一个普遍的观察：如果并发执行的线程以一致的顺序锁定对象,则死锁是不可能的.对于任何类型的锁定都是如此,无论是平均多线程程序中的互斥锁(例如,参见Herb Sutter’s thoughts on Lock Hierarchies),还是数据库.一旦你以任何两个锁被“翻转”的方式更改顺序,就会引入死锁的可能性.
在不扫描索引的情况下,您将按一个顺序更新(和锁定)行,并将索引更新为另一个顺序.所以,这可能是你的情况：
>如果为两个并发事务禁用索引扫描,它们都以相同的顺序[X]锁定行,因此不会出现死锁.
>如果仅为一个事务启用索引扫描,则它们不再以相同的顺序锁定行,因此可能发生死锁.
>如果为两个事务启用了索引扫描,那么它们都会以相同的顺序锁定行,并且死锁是不可能的(继续尝试更改会话集optimizer_index_cost_adj = 1;在两个会话中你都会看到).
[X]虽然我不会依赖具有保证顺序的全表扫描 – 但这可能只是当前Oracle在这些特定情况下的工作方式,而未来某些Oracle或不同情况可能会产生不同的行为.
因此,索引的存在是偶然的 – 真正的问题是排序.恰好在UPDATE中的排序可能会受到索引的影响,但如果我们能够以另一种方式影响排序,我们会得到类似的结果.
由于UPDATE没有ORDER BY,因此无法单独通过UPDATE保证锁定顺序.但是,如果将锁定与更新分开,则可以保证锁定顺序：
SELECT ... ORDER BY ... FOR UPDATE;
虽然您的原始代码在我的Oracle 10环境中导致死锁,但以下代码不会：
第一节：
declare
cursor cur is select * from deadlock_test where a > 0 order by a for update;
begin
while true loop
for locked_row in cur loop
update deadlock_test set a = -99999999999999999999 where current of cur;
end loop;
rollback;
end loop;
end;
/
第二节：
alter session set optimizer_index_cost_adj = 1;
declare
cursor cur is select * from deadlock_test where a > 0 order by a for update;
begin
while true loop
for locked_row in cur loop
update deadlock_test set a = -99999999999999999999 where current of cur;
end loop;
rollback;
end loop;
end;
/
