Re: row vs row.column level locking

On Sat, 17 Dec 2005 18:20:59 -0500, Serge Rielau wrote:

> Well, we agree on the price tag at least.
> Now I have no clue how statistical column correlation has anything to do
> with locking granularity.

Serge, my suspicion is the following: column C1 has some kind of relationship (business, logical, statistical?) with the column C2. Uncontrolled simultaneous update of the two columns by two unrelated processes might break that, possibly important, relationship thereby producing logical corruption of the database.

I know that relational theorists cringe on the thought of related (derivable) data in two different columns, but sometimes it is necessary to have that. In the large HMO that I was working for, medical providers (also known as "doctors") had two types of ID: new HIPPA Id and the old, "local" provider ID. There was also 1-1 mapping between them, the local ID was the primary key, while the new HIPPA ID was made a unique key. Those columns were obviously related, while the relationship among them was not immediately apparent. Any transaction which would update one, without updating another would break the mapping rules, which were crucial in the year 2003 (deadline for the HIPPA implementation). In other words, database which would allow independent updates of both columns simultaneously would have a serious potential to cause significant business damage, with all ramifications (fines up to $1M /day were threatened to HMOs which were not HIPPA compliant by the end of 2003).

What makes me particularly uncomfortable is transition from the relational model, which contains n-tuples as its most basic element to something that might be called "spreadsheet model" as each "cell" becomes individually addressable (and lockable) by its "coordinates". From the mathematical point of view, a relation is, simply put, a subset of a Cartesian product. Elements of a Cartesian product of sets A and B are n-tuples (or "ordered pairs") (a,b) where a belongs to A and b belongs to B. If you operate on a relation, you operate on its elements, which are n-tuples, not on its individual "coordinates". I'm also afraid that all ramifications of the switching the underlying mathematical model are not yet well understood and have potential to introduce tectonic changes which global business is not yet aware of. Also, in that case, I'd suggest changing name to SDBMS instead of RDBMS (Spreadsheet Database Management System).

-- 
http://www.mgogala.com