Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: lock Oracle db objects, but only a lock for others?

Re: lock Oracle db objects, but only a lock for others?

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Tue, 22 Apr 2003 15:59:17 GMT
Message-ID: <MPG.190f06afee4f8af3989745@news.la.sbcglobal.net>


emfortin_at_attbi.com said...
>
> That's exactly what we do. We set the 'session_id' = to the handheld
> identifier. There is no locking (beyond the original session id assignment)
> except for application logic that will not allow someone to select a job to
> download to their ready that is already download to another the handheld.
> (based on that session_id being set. And there is an admin report to show
> which job is assigned to which handheld (and which user)) When the user
> comes back and uploads the data, regardless of what happens, the job is
> freed (or completed) for the next user. And there is always query
> capability in the scenario.
>
> I guess I didn't explain well.
>

I snipped a lot because this thread was getting difficult to read. Unfortunately, there's little you can do from a database point of view, since Oracle's architecture assumes a persistent connection to correctly handle locks and referential integrity.

Also unfortunately, it sounds like you're faced with a spagetti-code application that has "grown" (bloated?) over the years into a mess. Well, at some point in time, your management will have to say they've had enough of the old way ... and take time out to redesign based on lessons learned over the years. After all, you know more today than you did when the app was first designed ... or thrown together into a prototype that became production because no one could take the time to design it.

It sorta reminds me of the good old flat-file days, before databases, when we had to design systems with a master file and one or more transaction files. It's also the situation faced by software repositories such as CVS or MS VSS. Your master is the database; your transaction files are the handheld or laptop files.

If I have a "modified date" column in my db (master), then I should copy that to my downloaded transaction files. Then when I want to upload my data, I compare the master's modified date against my transaction file's date. If the master date is the same as my date, then I know it's safe to upload. If the master date is later, then I know someone else modified the data after I downloaded it ... and I have to do a merge.

The trouble with a simple binary flag is that it doesn't supply enough info to make the merge work. How do you know which transaction file to apply to the master? In the flat-file days, we gathered all the transaction files together and merged/sorted them for a single batch update. When two transaction records attempted to update the same record, they were kicked out to an exception file to be resolved by a human. Your situation just might be complex enough to warrant falling back, partially at least, to the old flat-file, batch way of doing things.

-- 
/Karsten
DBA > retired > DBA
Received on Tue Apr 22 2003 - 10:59:17 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US