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

On Mon, 21 Apr 2003 14:44:43 GMT, "Eric Fortin" <emfortin_at_attbi.com> wrote:

>
>
>"Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
>news:79v7av8ldp39d5jpcv86l90lck6khs0ljg_at_4ax.com...
>> On Mon, 21 Apr 2003 13:06:40 GMT, "Eric Fortin" <emfortin_at_attbi.com>
>> wrote:
>>
>> >
>> >Again, I think this is clearer and easier to maintain than coded flags.
>> >
>>
>> Good luck then with re-inventing the wheel and trying to screw up
>> Oracle on a structural basis.
>>
>>
>> Sybrand Bakker, Senior Oracle DBA
>>
>> To reply remove -verwijderdit from my e-mail address
>
>What's the better solution? And how is that re inventing the wheel. We
>have rows that need to be worked on by a single user and only a single user
>for extended periods of time. And the table still needs to be accessible
>for other processing. How would you problem solve this? I'd like to see an
>example or doc on this. I searched tahiti.oracle.com, but kept getting no
>matches.
>
>(We've got users that take a handheld, go out into the field (literally, to
>work on pipeline and/or oil wells), and come back 10 hours later. We don't
>know whether or not they will get one of the jobs, half of the jobs, or all
>of the jobs done. And we don't know the onsite intangibles. What's wrong
>with setting a column that essentially says, this row is being worked on and
>must stay in this state until the worker is done). How do you handle this
>scenario in the database without setting something? In this example, there
>isn't any code variables that can be set. The devices are all disconnected
>and their own entitiy.
>
>And what about a web data entry form that is accessed by many users? (Say
>an order header form)How do you prevent additional users from over writing
>the changes of the initial user. (user 1 brings up the header info and
>decides he wants to change the credit card information) User 2, working a
>similar problem with all orders, brings up that order screen (and happens
>upon that specific order) and saves it fifteen minutes after user 1 (what
>happens to the credit card information. Which is the correct credit card
>number? the one saved by user 1 or the one saved by user 2). Certainly
>others have come across this problem. (I would bet that the solution for
>this scenario would apply to the above hand held scenario). How do other
>people do this (assuming that setting a column on that row is the wrong way
>to go about it, and I know that it causes havoc when the user closes the web
>browser and the column stays populated)?
>
>What is the right way so I don't create any more problems.
>

First of all: Oracle has multi-versioning. This means the end-user will always get to see the correct version of the record. In Oracle readers don't block writers, in sqlserver readers do block writers. You seem to have been exposed to sqlserver. Get the Expert-on-one book by Thomas Kyte and read the first three chapters. It's all there!!!
You should lock a row only when you really are going to change anything. NOT when you SELECT a row. We use a generic application which can run against any database implemented this way, and their 'locking' (by means of the flag mechanism you describe) is a REAL pain in the ass. It means no 2 readers can ever read the same record, even if they don't plan to save anything.

Oracle has mechanisms to abort a session which is inactive for a prolonged period of time. Allowing a lock to stay for 10 hours is just plain ridiculous.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Apr 21 2003 - 12:26:25 CDT