Re: oracle concurrent

From: Tom Cooke <tom_at_tomcooke.demon.co.uk>
Date: 1996/02/06
Message-ID: <9ADQ9BA4E8FxEwT6_at_tomcooke.demon.co.uk>#1/1


In article <odysscci.575.000C4F29_at_teleport.com>, Jim Kennedy <odysscci_at_teleport.com> writes
>In article <0oTqEFAus7DxEwQB_at_tomcooke.demon.co.uk> Tom Cooke
><tom_at_tomcooke.demon.co.uk> writes:
>>Path: nntp.teleport.com!news.reed.edu!news.orst.edu!news.uoregon.edu!news.dacom
>.co.kr!usenet.seri.re.kr!news.cais.net!xara.net!peer-news.britain.eu.net!demon!t
>omcooke.demon.co.uk!tom
>>From: Tom Cooke <tom_at_tomcooke.demon.co.uk>
>>Newsgroups: comp.databases.oracle
>>Subject: Re: oracle concurrent
>>Date: Wed, 31 Jan 1996 18:55:42 +0000
>>Organization: North Staffordshire Hospital NHS Trust
>>Lines: 21
>>Distribution: world
>>Message-ID: <0oTqEFAus7DxEwQB_at_tomcooke.demon.co.uk>
>>References: <4e1jpi$b6a_at_cnct.com> <4e1kbv$cb7_at_cnct.com>
>> <310FAE65.1232_at_us.oracle.com>
>>NNTP-Posting-Host: tomcooke.demon.co.uk
>>X-NNTP-Posting-Host: tomcooke.demon.co.uk
>>MIME-Version: 1.0
>>X-Newsreader: Turnpike Version 1.11 <91JPjwBJDWIUxzmtiYJRSjsG0G>
>
>
>>In article <310FAE65.1232_at_us.oracle.com>, "Robert C. Nix"
>><rnix_at_us.oracle.com> writes
>>>> lee (lee_at_cnct.com) wrote:
>>>> problem:
>>>> how to avoid two user SELECT same data from database,
>>>> and one's UPDATE + COMMIT overwrite another one's UPDATE +
>>>> COMMIT ?
>>>> we don't want lock the row after SELECT, but want lock
>>>> the row after UPDATE, and before COMMIT, how to do
>>>> this ?
>>>> Thank you.
>>>
>>>one option is to add a date_modified column to the table.
>>> stuff deleted
 

>>Sounds like bad business process/application design - which user has the
>>"correct" data i.e. accurately represents some aspect of the real world,
>>which is what databases are for, no?
>>--
>>Tom Cooke
>
>Let us say that you have a person table with the following fields:
>
>ssn,name, address,sex where ssn is the primary key
>
>Let us say that the data is:
>111-11-1111,Bob,123 Main Street,M
>
>User 1 and user 2 selects bob's info.
>User 1 changes Bob's address to 123 Maple Street. So user one issue the
>follwoing update statement
>
>update person set address='123 Maple Street' where ssn='111-11-1111' and
>name='Bob' and address='123 Main Street' and sex='M';
>commit;
>
>In the mean time user 2 changes the address also. User two issues the
>following update statement
>update person set address='123 Oak Street' where ssn='111-11-1111' and
>name='Bob' and address='123 Main Street' and sex='M';
>
>User 2's update statement will return with 0 rows processed because the data
>was changed underneath him. Thus the system can alert user 2 to what occured
>and ask him what to do.
>
>Jim Kennedy
>

Hang on! It doesn't work like this! Certainly if you're using a forms- based product, the update statements are not in this form; instead, rowlevel  locking is used. What you actually get is something like "Attempting to lock... Failed" if you try and update it, or "Data has been changed by another user - please requery", depending on who did what first. Would anybody like to comment on my original point, i.e. the design issues - how does the business define who has the right data?

-- 
Tom Cooke
Received on Tue Feb 06 1996 - 00:00:00 CET

Original text of this message