On Wed, 12 Nov 2003 10:54:05 -0800, Emerson, Tom
<[log in to unmask]> wrote:

>> -----Original Message-----
>> From: Eben Yong [mailto:[log in to unmask]]
>>
>> We are an HMO that makes use of the SSN to uniquely identify
>> a member.  A new California law going into effect in mid-2004 limits
the
>> usage of the SSN [...]
>
>A new law?  I seem to recall as I was getting into college [>ahem< some
time ago...] that this practice was considered a no-no [and besides, what
do you do for folks that don't have such a number, such as foreign
nationals or just plain ornery folk that don't want one?  Though I imagine
in your case "foreign nationals" should be a rather rare occurance :)]

A complex issue.  In fact, the SSN in California Medi-Cal is known as the
MEDS ID.  The MEDS ID is not always the SSN, but the SSN is always the
MEDS ID.  The point, here, is that the MEDS ID is assigned by the State--
and we are replacing the MEDS ID with another ID.

>
>> I am ... writing a program that will ... change [the old number to the]
>> correlating "new" ID number.  This will happen over multiple
>> datasets and databases [...]
>> If the program is run off-hours, there's no problem ...
>> run it during OLTP, someone might ... only find half of them
>> because the other half has already been "changed" ...
>
>That's the thing about "non-zero probabilities" -- you say it takes about
an hour to process 100 members, or roughly 36 seconds each [3600
seconds/hour / 100]  that is a 30 second+ window in which that PARTICULAR
member is being researched would result in a problem.  You also mentioned
it will take "several days" to process all member records, so a SWAG on
that would be 25,000 members in the database? [seems low -- my "guess" is
10 days worth of work * 24 hours * 100 members/hour, then fudged up to a
nice number]  So, in any given 30-second window, there is a 1-in-25,000
chance that THAT particular member needs to be researched. (well, "1-
in..." times the number of requests that appear per half minute, campus
wide...)

Pretty close.  We actually have closer to 45,000 people members.

>
>Sure, the chances are small, but "non-zero", [and if murphy has his way,
it will be the most influential member who gets his "research" screwed up
in that 30 seconds...]
>
>> Thus the question, is it possible to row-level lock all rows in all
datasets that
>> pertain to this particular member, change all of them, then commit all
>> transactions?
>
>Yes almost -- you CAN perform a DBLOCK on a specific record, and you
should even be able to lock several dataSETS at once with that
same "descriptor", however you cannot lock TWO seperate dataBASES without
special programming [basically, a waiver from the system allowing you to
potentially DEADLOCK things]  Furthermore, this lock is somewhat advisory -
- it only prevents other processes from WRITING to these records (because
a lock is always required for writing), it doesn't actually prevent them
from READING [or attempting to read] them.  OTOH, if "all programs were
playing by the rules", then the other programs would attempt to perform
the exact same lock BEFORE READING to ensure the record(s) are available.
However your next comment:
>
>> The other applications would not "know" that we are row-level locking
>
>tells me that this is not the case.  It also suggest that "set level"
locking it being used, perhaps by a series of "wrapper" calls that perform
the DBLOCK/PUT-or-UPDATE/UNLOCK all at once.  As noted previously, this
basically means that your carefully planned item (row-level) locking will
degrade into generic set-level locking in practice.
>

Yes.  It seems to me that it's best, in this scenario, to stay with the
original plan.  Since I'm OK with running the conversion program off-hours
and that meets the business need, then stick with that plan.

* To join/leave the list, search archives, change list settings, *
* etc., please visit http://raven.utc.edu/archives/hp3000-l.html *