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


"Serge Rielau" <srielau@xxxxxxxxxx> wrote in message
news:40jrokF1b00tlU1@xxxxxxxxxxxxxxxxx
> 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


.

Relevant Pages

  • Re: Help - Timing Logic
    ... server application, both of which ran on the same box. ... the client applications and 'lodging' them in the database. ... Another part of the server application was dedicated to retrieving messages ... commit transaction ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... put a trigger on the database table. ... Transaction and Database Locking - look at isolation levels / settings ... Maybe implement a message broker ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... build a trigger on the database .. ... Transaction and Database Locking - look at isolation levels / settings ... If you need to implement a locking mechanism / or / logging mechanism / or / a checking mechanism to avoid duplicate messages caused by multi-threading ... ... message broker gets all the necessary messages or message id's from the database to be sent ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... put a trigger on the database table. ... Transaction and Database Locking - look at isolation levels / settings ... Maybe implement a message broker ... ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Help - Timing Logic
    ... If you need to implement a locking mechanism / or / logging mechanism / or / a checking mechanism to avoid duplicate messages caused by multi-threading ... ... Is it the READ from the database ... ... Perhaps a stored proc may be faster to execute and return the values as opposed to building the transaction in the code. ... implement as above locking only the records you retrieve / update - need to watch out here for table locking ... ...
    (microsoft.public.dotnet.languages.vb)