Re: Configure Oracle ODBC to not wait for locks

[Quoted] [Quoted] Ahh. I see. No, the description is much clearer. I don't think I need to [Quoted] see any code. There are several ways to take care of this. You could choose to not use the for update in the select. (both in the scanning process and in the application that is looking at rows.) I know [Quoted] you don't want user A to read a row, make a decision and change the row that [Quoted] was changed underneath him. (a sensible concern) Instead of the update or delete being
update mytable set ... where rowid=
do
[Quoted] update mytable set... where rowid= and col1=original_value and col2=original_value...
If the value has changed underneath you then you will find out that you have [Quoted] 0 rows updated. (since you specify rowid, it will find the row effeciently) [Quoted] You can then choose to deal with that situation. You can do that also with [Quoted] the scanning piece. It won't delete a row that has changed.

[Quoted] Now that I think about it why not just delete the rows instead of scanning them.
[Quoted] delete from mytable where <date field> < <some date> ;

But there might be some reason why not. (some business requirement)

Jim

"Mark E. Hansen" <meh_at_NOSPAMunify.com> wrote in message news: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
>
> Sorry if I wasn't clear. The application is supposed to scan a table
 looking
> 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"
>
> 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
> by updating it or also performing a "select for update" of it, then my
 execute
> 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
> > > > 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 Sat Dec 02 2000 - 07:36:08 CET