RE: select for update

Hi G.

We had a similar problem with an application that didn't support the update command using load balancing. Commit command came back on a different channel than update was sent: Table was locked forever... (but it is solved now).

I don't know if 8.1.6. has a feature to do this but as a temporary solution we created a procedure run as a DBMS job to automatically kill jobs that were blocking for a specific time. You can specify you own selection criteria for which session to kill. You will need to use some dynamic SQL for this solution.

If you're interested in this solution I can sent you the package code.

Regards,

Ronald Hijdra
Oracle DBA DutchTone N.V.
P.O. Box 95 313 - 2509 CH Den Haag - The Netherlands phone: +31 70 889 9886
mobile +31628024211
Fax: +31 70 889 9955
email: ronald.hijdra_at_dutchtone.nl

> -----Original Message-----
> From: guy ruth hammond [SMTP:grh_at_agency.com]
> Sent: dinsdag 18 juli 2000 22:49
> To: Multiple recipients of list ORACLE-L
> Subject: select for update
>
> Hi all,
>
> I've got a bit of a weird problem. I have a bunch of user connecting to
> Oracle 8.1.6 via a middleware layer, which provides a connection pool.
> The code they are running contains lots of SELECT FOR UPDATE statements,
> because we need to ensure that things happen in sequence, rather than
> a situation in which process 1 can issue an UPDATE, process 2 issue a
> plain SELECT, then process 1 COMMIT.
>
> The processes connect to middleware, then middleware connects to Oracle,
> so if a process dies (which happens from time to time), Oracle doesn't
> know. If a process requests a lock, then dies, Oracle will hold that
> lock, and all the other processes will be stalled. We know that if
> a process is successful, it will never require a lock for longer than
> 2 seconds, but we allow 10 seconds.
>
> So my problem is, is there a way to tell Oracle to automatically
> free any locks once they reach a certain age?
>
> Thanks,
>
> g
>
>
> --
> guy ruth hammond <grh_at_agency.com> | One is punished for being
> Technology Analysis & Consulting | weak, not for being cruel.
> 617 4521300 http://www.agency.com | -- Baudelaire
> --
> Author: guy ruth hammond
> INET: grh_at_agency.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Wed Jul 19 2000 - 01:54:03 CDT