Re: row vs row.column level locking ++ constraints and TAPIs

"Serge Rielau" <srielau_at_ca.ibm.com> wrote in message news:40jrokF1b00tlU1_at_individual.net...
> Mladen Gogala wrote:
>> On Sat, 17 Dec 2005 18:20:59 -0500, Serge Rielau wrote:
>>
>>

...

>> 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).
>>

...

>>
> In order for this application to be dangerous it would have to enable
> updating only one of the columns in this functional dependency to begin
> with. That is if only one "user" is connected.
> Since to change both columns (or cells is a pivoted table) need to be
> locked. There is no danger there I believe as long as read transactions
> are properly implemented by the RDBMS (that is read locks but be held to
> enforce serializability).
>

...

>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> DB2 UDB for Linux, Unix, Windows
> IBM Toronto Lab

Just a couple important concepts that have a bearing on the issue and this example.

1. column dependencies & constraints -- these don't violate relational design (only if a column's value is fully derivable from other column values, not if its value, or the need to change its value, is affected by the values of other columns). these are fully implementable, and should be implemented, in current systems, regardless of locking mechanism
2. TAPIs -- few applications (should) allow direct end-user DML for any significant business transaction. Any database that does not have proper constraints (declarative or triggered) and allows carte-blanche insert update delete directly against the tables is subject to logical data corruption. Additionally, some sort of business logic layer (tier) somewhere between the user and the data must be responsible for any data and transaction integrity issues that cannot be enforced by triggers and constraints -- preferably implemented in database packages and procedures.

So no new issues in these areas if and when 'CLL' is implemented.

++ mcs Received on Sun Dec 18 2005 - 07:08:41 CST