There is well-known bug with “for update order by” on 11.2, when rows locks not in specified order, although the ordering occurs.
I already wrote on my russian blog about the appearance of “buffer sort” in plans with “for update” even if sort order was not specified. And this behavior can be disabled for example by specifying /*+ opt_param( ‘optimizer_features_enable’ ‘11.1.0.7’ ) */.
But if we want to solve problem with deadlock, we need to force index full scan/index range scan ascending with “buffer sort” usage.
UPD: The patch is already available for 11.2.0.2 and 11.2.0.3: Patch 13371104: LOCK ORDER HAS CHANGED BETWEEN 10.2.0.5 AND 11.2.0.2.
create table test24 ( id int primary key );
insert into test24 select rownum from all_objects where rownum <= 3;
create or replace procedure p_test24( p in boolean ) is
a sys.ku$_objnumset; -- it is just table of number
begin
if p then
a := sys.ku$_objnumset( 1, 2, 3 );
else
a := sys.ku$_objnumset( 3, 2, 1 );
end if;
for i in (
select o.id
from table( a ) t
join test24 o on o.id = t.column_value
order by o.id
for update of o.id
)
loop
null;
end loop;
commit;
end;
/
Now in one session:
begin
loop
p_test24( true );
end loop;
end;
/
And in another:
begin
loop
p_test24( false );
end loop;
end;
/
After it we will get
ERROR at line 1: ORA-00060: deadlock detected while waiting for resource ORA-06512: at "XTENDER.P_TEST24", line 10 ORA-06512: at line 3
Plan for this query:
SQL_ID gm3j14n5nq6hx, child number 0
-------------------------------------
SELECT O.ID FROM TABLE( :B1 ) T JOIN TEST24 O ON O.ID = T.COLUMN_VALUE
ORDER BY O.ID FOR UPDATE OF O.ID
Plan hash value: 96499627
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 30 (100)| |
| 1 | FOR UPDATE | | | | | |
| 2 | SORT ORDER BY | | 3 | 45 | 30 (4)| 00:00:01 |
| 3 | NESTED LOOPS | | 3 | 45 | 29 (0)| 00:00:01 |
| 4 | COLLECTION ITERATOR PICKLER FETCH| | 3 | 6 | 29 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0017484 | 1 | 13 | 0 (0)| |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("O"."ID"=VALUE(KOKBF$))
Same plan we can get on 11.2.0.1, but rows will be locked in right order without bug.
But if we change query:
create or replace procedure p_test24( p in boolean ) is
a sys.ku$_objnumset; -- it is just table of number
begin
if p then
a := sys.ku$_objnumset( 1, 2, 3 );
else
a := sys.ku$_objnumset( 3, 2, 1 );
end if;
for i in (
select/*+ leading(o t) use_nl(o t) */ o.id
from
(select/*+ no_merge */ * from table( a )) t
,test24 o
where o.id = t.column_value
order by o.id
for update of o.id
)
loop
null;
end loop;
commit;
end;
/
Problem will be solved. Plan will be changed to:
SQL_ID guzu6v2usmsv7, child number 0
-------------------------------------
SELECT/*+ leading(o t) use_nl(o t) */ O.ID FROM (SELECT/*+ no_merge */
* FROM TABLE( :B1 )) T ,TEST24 O WHERE O.ID = T.COLUMN_VALUE ORDER BY
O.ID FOR UPDATE OF O.ID
Plan hash value: 1767001628
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 89 (100)| |
| 1 | FOR UPDATE | | | | | |
| 2 | BUFFER SORT | | | | | |
| 3 | NESTED LOOPS | | 3 | 78 | 89 (0)| 00:00:02 |
| 4 | INDEX FULL SCAN | SYS_C0017484 | 3 | 39 | 2 (0)| 00:00:01 |
|* 5 | VIEW | | 1 | 13 | 29 (0)| 00:00:01 |
| 6 | COLLECTION ITERATOR PICKLER FETCH| | 3 | 6 | 29 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("O"."ID"="T"."COLUMN_VALUE")