Re: Update with wait for lock

From: <johnbhurley_at_sbcglobal.net>
Date: Fri, 1 May 2009 11:16:44 -0700 (PDT)
Message-ID: <13416158-df24-4fb0-9ba2-4769fde7ca7f_at_m19g2000yqk.googlegroups.com>



On May 1, 2:06 pm, DBA100 <dba..._at_gmail.com> wrote:
> Is it possible to instruct oracle that, while updating, wait for a max period
> of 10 seconds if the row is locked by another session. For e.g in DB2
> I would do this
>
> SET LOCK MODE TO WAIT 10
> UPDATE ....
>
> In this case, if Db2 finds that the row being updated has a lock
> by another session then it will keep trying to acquire the lock
> for a max period of 10 seconds.
>
> I could not find anything similar in Oracle. The closest I see
> is using SELECT FOR UPDATE which is not the same as UPDATE.

The facilities Oracle provides do not match directly against other vendors implementations of locking.

You can cover your update by using pessimistic locking ( get the locks up front ) by doing the SELECT FOR UPDATE WAIT X prior to your UPDATE.

Tom Kyte has a good book "Expert Oracle Database Architecture" that gives you a lengthy detailed understanding of how this all works in oracle. Before you get too deep into details of planning how to port an application you really should read that at least a couple of times. Received on Fri May 01 2009 - 13:16:44 CDT

Original text of this message