Database Query Contains Old Data

[James A. Donald]

On Mon, 02 Jun 2008 20:59:09 -0700,  <wlfraed at ix.netcom.com> wrote:

James A. Donald 
> > If one has transactions open for a long time, or transactions that
> > involve a great deal of data, this will result in poor performance or
> > poor scalability.  But one may have such large transactions without
> > being aware of it.  Is there any way to make transaction size salient
> > to the developer?  Any way to make sure one is committing as early and
> > often as possible?

Dennis Lee Bieber
> 	I would tend to suggest that one code to collect all needed
> information for a complete transaction /before/ even starting a
> transaction; then one can start it and feed all the operations in short
> order.
> 
> 	Now, if the application requires doing something like:
> 
> select set of data
> repeat until user is done
> 	display by record to user
> 	wait for user response
> 	issue update for record
> commit transaction
> 
> this becomes problematic.

Well obviously one should do the former and not the latter, but one
may well start out doing the former and gradually drift into doing the
latter without entirely being aware of it.

> 	A more complex method would be:
> 
> select set of data; save in local storage; commit
> repeat until user is done
> 	display saved record to user
> 	wait for user response
> 	save modified record (second storage)
> repeat until no conflicts
> 	select original set again
> 	repeat until done
> 		if saved original <> reselected original
> 			if saved original had been modified by user
> 				User interaction: 	override other changes
> 						or redisplay and get edits from user
> transaction begin
> issue all updates
> commit

Having drifted into doing things the wrong way without being aware of
it, one would then wait until ones application comes to a grinding
halt due to scaling problems, figure out what was causing the problem,
and then recode by the more complex method you describe.  At that
point, however, one would belatedly discover that one's schema made
the more complex method hideously complex for the programmer and
incomprehensible to the user, and that it was far too late to change
the schema.

I suppose there is no help for it but to be mindful of scaling and
transactions from the beginning.

--
  ----------------------
We have the right to defend ourselves and our property, because 
of the kind of animals that we are. True law derives from this 
right, not from the arbitrary power of the omnipotent state.

http://www.jim.com/      James A. Donald