Re: Ora-00060 Deadlock Problem. Select for update behavior.

Martin Haltmayer wrote:

> Comments inline.
>
> Madhu Konda wrote:
> >
> > ...
> >
> > When using "select ... for update" should not processB wait till processA
> > commits. So technically, they should not see the same rows at all because there is
> > only one process working on the table at any time. Please correct me if I am wrong.
>
> Yes, you are not totally correct. B may not *finish* until A commits. But it may
> *start* its work. And if both start the sequence they proceed is undefined. If
> the processing sequence of the rows were always the same no deadlock would occur
> but one process B would wait for A or vice versa.
>

I tested this situation which is exactly what I described. Create table as: create table dl (col1 number, col2 varchar2(200)); SessionI: Time T1

                insert into dl values(1, 'test1');
                insert into dl values(2, 'test2');
                insert into dl values(3, 'test3');
                commit;

SessionA: Time T2
SQL> select * from dl order by col1 for update;

      COL1 COL2

         1            test1
         2            test2
         3            test3

SessionB: Time T3
SQL> select * from dl order by col1 for update; Hangs and waits.

SessionI: Time T4
SQL> insert into dl values(4,'test4');
1 row created.
SQL> commit;
Commit complete.

SessionA: Time T5
SQL> delete from dl where col1 in (1,2,3); 3 rows deleted.
SQL> commit;
Commit complete.

SessionB: Time T6 after T5 goes thru since Session A finished. SQL> select * from dl order by col1 for update;

      COL1 COL2

         4 test4

Not sure what you mean by saying Session B can *start* its work. Session B was waiting and

when Session A commited, it can see only 4 which was inserted in the meanwhile. What is happening inside the covers. How can this cause a deadlock. Appreciate it if you can clarify your statement.

Thanks
Madhu

>
> >
> > ...
> >
> > Since I cannot issue exclusive lock because inserts are happening, I can issue "lock
> > table in row share mode" which is the same lock as select .. for update. Is there
> > a difference in one vs the other?
>
> Yes, the share lock will not keep the second process B from starting.
>
> Try with "delete ... where rownum <= 1".
>
> Martin
  Received on Thu Jun 07 2001 - 13:59:39 CDT