Re: Configure Oracle ODBC to not wait for locks

From: Mark E. Hansen <meh_at_NOSPAMunify.com>
Date: Fri, 01 Dec 2000 08:22:28 -0800
Message-ID: <3A27D044.5BB742F6_at_NOSPAMunify.com>


Barbara Kennedy wrote:
>
> Do you mean for update? I believe you put in the sql statement nowait
> update mytable set myfield=7 where ... nowait. I don't know where you set
> it up in ODBC.
> But you say access which means read, and I have never seen readers get
> blocked in Oracle (SQL Server, DB2 sure). I would look at the ODBC api
> documentation 'cause I don't know.
> Jim

[Quoted] Sorry if I wasn't clear. The application is supposed to scan a table looking [Quoted] for old rows, and delete them when found. I've implemented this with a

"select <field>, rowid from table where <date field> < <some date> for update"

[Quoted] Then, I do an SQLFetch() in a loop to get all the records. For each record I plan to do a "delete where rowid = ..." statement. However, right now I'm just incrementing a counter (until I get this problem resolved).

The problem is, if another process has a lock on one of the records, either [Quoted] by updating it or also performing a "select for update" of it, then my execute [Quoted] or fetch hangs, waiting for the conflict to be resolved.

My intention is to skip any records that are currently being locked by another process, but as it stands my entire process hangs until the other process commits it's transaction.

When I did a similar job using OCI, I was able to configure the cursor to not wait for conflicting locks. Instead, if such a condition arose the statement would immediately return an error (lock conflict).

Is there any way I can accomplish the same thing using the ODBC interface?

By the way, if you were serious about seeing my code, I can put together a simple application that would show the problem...

>
> "Mark E. Hansen" <meh_at_NOSPAMunify.com> wrote in message
> news:3A267074.42EB577E_at_NOSPAMunify.com...
> >
> >
> > Barbara Kennedy wrote:
> > >
> > > I think you mean for update. Readers don't wait for writers in Oracle.
> > > Jim
> >
> > So do you know how to configure the cursor so it won't block waiting
> > for the lock conflict to subside?
> >
> >
> > > "Mark E. Hansen" <meh_at_NOSPAMunify.com> wrote in message [Quoted]
> > > news:3A2585C5.FE2E976_at_NOSPAMunify.com...
> > > >
> > > > I would like to configure my ODBC application to not block when
> > > > attempting to access rows in the Oracle database that have conflicting
> > > > locks.
> > > >
> > > > In an OCI application, I make a call to "oopt" to set two options on
> > > > the cursor: ROW_RLBACK and DONOT_WAIT (these get or'ed together). This
> > > > causes the operation to return immediately with an error if a lock
> > > > conflict is encountered.
> > > >
> > > > However, I can't find any way to do this from my ODBC application.
> > > > Can anyone tell me how to accomplish this?
> > > >
> > > > Regards,
Received on Fri Dec 01 2000 - 17:22:28 CET

Original text of this message