Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!tut.cis.ohio-state.edu!gem.mps.ohio-state.edu!ginosko!rex!uflorida!haven!vrdxhq!daitc!daitc.daitc.mil
From: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Newsgroups: comp.databases
Subject: Re: Concurrency control in real life
Keywords: concurrency updates
Message-ID: <614@daitc.daitc.mil>
Date: 28 Jul 89 12:49:05 GMT
References: <322@csense.UUCP>
Sender: jkrueger@daitc.daitc.mil
Reply-To: jkrueger@daitc.daitc.mil (Jonathan Krueger)
Organization: DTIC Special Projects Office (DTIC-SPO), Alexandria VA
Lines: 64
In-reply-to: bote@csense.UUCP (John Boteler)

In article <322@csense.UUCP>, bote@csense (John Boteler) writes:
>I request that someone post a solid example of an instance
>where concurrency is a problem to control, perhaps with
>a discussion of concurrency issues relating to the example.

There are several concurrency problems.  They may be described in
three broad groups: the lost update problem, the uncommitted
dependency problem, and the inconsistent analysis problem (following
Date, An Introduction to Database Systems, Volume I, Fourth Edition,
Addison-Wesley, 1986).
	
>I am trying to envision 10 stock traders all scrambling 
>simultaneously to write the latest price of an issue on a 
>blackboard in a busy Manhattan center...how many can actually
>have the right price? The latest price?

The market isn't a good source for examples.  It's too complex.  And
as you point out, the real world supplies natural serializing
mechanisms.  But I'll try.  Consider a table like:

	+-------+
	| stock | owner    shares   company
	+-------+--------+--------+----------+
	        |  Smith |  5000  | Sony     |
	        |  Jones |  5000  | Sony     |
	        |  Trump | 40000  | Sony     |
	        +--------+--------+----------+

We run a report to tell us where the sharks are:

		owner	company	ownership
		-----	-------	---------
		Trump	Sony	80%

Now Sony issues 50,000 more shares and Trump buys them:

	+-------+
	| stock | owner    shares   company
	+-------+--------+--------+----------+
	        |  Smith |  5000  | Sony     |
	        |  Jones |  5000  | Sony     |
	        |  Trump | 90000  | Sony     |
	        +--------+--------+----------+

Now, what will the report say?  Well, there are two correct answers,
either 90% or 80%.  The first is correct if the row has been updated
before the report is run, the second if the opposite is true.  There
are also two incorrect answers: 40% or 180%.  The first happens if we
fetch Trump's ownership before the update occurs and compute the
aggregate after, the second if the opposite is true.

Both incorrect answers are examples of the inconsistent analysis
problem.  They can be prevented by various methods of concurrency
control.  A common method is locking.  If the report is in progress
when the update is submitted, the update is locked out until the
report is completed.  If the update is in progress when the report is
started, the report is locked out until the update is committed or
rolled back.

Does this help?

-- Jon
-- 
--