Re: oracle concurrent

From: MPower 'your future today' -- Mark Otero <mpower_at_ee.net>
Date: 1996/02/08
Message-ID: <4fddjh$dl9_at_news.ee.net>

Tom Cooke <tom_at_tomcooke.demon.co.uk> wrote:

>>><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 ?



Let's get this correct.
1. Assumption: 
	The two users are connected to a single database.
	User 1 and User 2 both select the same data from the same
	table. User 1 and User 2 perform an update. Depending 
	on which user, 1 or 2, begins the update first and how long
	the update takes will determine if the other user's update
	will succeed or fail. 

	Implicit locking occurs when an UPDATE begins and remains
	in effect until a COMMIT or ROLLBACK statement is preformed.
	Therefore, while the UPDATE is preforming the other user will
	be unable to UPDATE (can't get a lock). Any SELECTs performed
	while the UPDATE is in process will return pre-update data. This
	means, if the other user SELECTs at this time they will not see
	the changes being made by the other user. So using a flag column
	in the table to prevent the second update will not work.
	
	The application must detect this condition and act accordingly.
	If your goal is to prevent the other user from overwriting the
	data at the conclusion of the other user's UPDATE then design 
	the application using the SELECT method described below. This 
	will prevent the selection of pre-update data not containing
	the correct flag.

2. The SELECT methods.
(ref. Oracle's SQL Statement Reference V6.0 pg. 5-131)

	Use the "SELECT...FOR UPDATE., in which case locks the selected rows
	of the tables. Once you lock a row, others cannot lock or
	update it until you free it with a COMMIT or ROLLBACK statement.
	Others will only see the changes after you commit them.

	The added option NOWAIT makes SELECT...FOR UPDATE terminate, rather
	than wait, if a row selected by FOR UPDATE OF cannot be locked 
	because someone	else is using it.



>>>>>               we don't want lock the row after SELECT, but want lock

>>>>>               the row after UPDATE, and before COMMIT, how to do


>>>>>               this ?



	As described the SELECT...FOR UPDATE will provide the lock.
	Users will still be able to SELECT...FROM. And the other user
	trying update will not be able to until after the UPDATE and
	COMMIT are performed. 

	You can still SELECT but you can not SELECT...FOR UPDATE.

>>>>>	...and before COMMIT..

	This phrase is invalid rows are unlocked after a COMMIT if locked.

	///////////////////////////
	Update Logic
	///////////////////////////
	if SELECT...f..FOR UPDATE FROM X
		if we_can_update(f) 
			f = current_time
			UPDATE...f...X
			.
			.
			.		 
			COMMIT
		else
			"Sorry, record just updated, come back tomorrow."
		fi
	else
		"Sorry, record locked, try later."
		
	fi
	////////////////////////////

	The question still looms as to what is really suppose to happen
	reqarding the business process being described -- two users updating
	the same data element. Who has the correct data, user 1 or user 2.
	
	When user 1 commits, user 2 is now free to update and thus overwrite
	the previous update by user 1. This is a business process issue and
	not a database issue. Does garbage in garbage out ring any bells.
	
	Someone must take ownership of the data and prevent such chaos.

	This may not apply to your process, you may have a valid reason
	for such a flow, I do not know.

	I hope this clears the muddy waters.

	Mark


===================================================
                       !!!!!
                      /'_at_ @'\

+---------------oOOo-----U-----oOOo---------------+

|   BigDaddy's Internet USENET FaxBack Service    |
|         'never leave home without it'           |
|                                                 |
| Mark Otero                        mpower_at_ee.net |
| MPower                        Your Future Today |
| 178 Beechbank Road               (614) 338-1550 |
| Columbus, Ohio 43213-1261    Fax (614) 338-1553 |
+-------------------------------------------------+
                     | |  | |
                    {__|  |__}

Received on Thu Feb 08 1996 - 00:00:00 CET