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

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Thu, 07 Jun 2001 02:05:00 +0200
Message-ID: <3B1EC52C.400055D5@0800-einwahl.de>

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.

>
> ...
>
> 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 Wed Jun 06 2001 - 19:05:00 CDT