Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Transaction level commits?

Re: Transaction level commits?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 15 Oct 1999 22:10:33 +0100
Message-ID: <940022637.21896.0.nnrp-13.9e984b29@news.demon.co.uk>


Perhaps we are talking at cross purposes - the following is a live session from SQL*Plus on 8.1.5 -
Deadlock reported after about 3 seconds

SQL> drop table test1;

Table dropped.

SQL>
SQL> create table test1
  2 unrecoverable
  3 as
  4 select rownum id, object_name
  5 from all_objects
  6 where rownum <= 20;

Table created.

SQL>
SQL>
SQL> create or replace procedure update_row as
  2 pragma autonomous_transaction;
  3 begin
  4 update test1
  5 set object_name = 'xxx'
  6 where id = 1;
  7
  8 commit;
  9 end;
 10 /

Procedure created.

SQL>
SQL> declare
  2 m_object_name varchar2(32);
  3 begin
  4 select object_name into m_object_name   5 from test1
  6 where id = 1
  7 for update of object_name
  8 nowait;
  9
 10 update_row;
 11
 12 commit;
 13 end;
 14 /
declare
*
ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "DEMO.UPDATE_ROW", line 4
ORA-06512: at line 10


SQL> spool off

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Doug Cowles wrote in message <380788FC.9F059421_at_nospambigfoot.com>...
>Actually, what seems to happen when I actually try this, is that TXN B just
sits
>there blocked. Am I not waiting long enough? Is the block indefinite, or
after
>a period of time (how long), will the deadlock ensue. I thought deadlock
was
>only for deadly embrace situations where TXN A was also waiting on TXN B.
In
>the example below, TXN A isn't dependent on TXN B, it's just not doing
anything
>with it's lock. Any comment?
>
>
Received on Fri Oct 15 1999 - 16:10:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US