Oracle SQL
  • LICENSE

Workaround for deadlock with select for update order by on 11.2.0.2-11.2.0.3

Posted on February 16, 2013 by Sayan Malakshinov Posted in bug, oracle 2 Comments

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")

bug deadlock for update
« Why between to_date(‘1582-10-15′,’yyyy-mm-dd’) and to_date(‘1582-10-04′,’yyyy-mm-dd’) only one day
Controlling “direct path reads” decision with INDEX_STATS/table_stats »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL