Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: row vs row.column level locking

Re: row vs row.column level locking

From: <>
Date: 19 Dec 2005 06:50:56 -0800
Message-ID: <>

Serge Rielau wrote:
> Mladen Gogala wrote:
> > 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).
> >
> 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).
> When we take into account non serializable isolation levels then things
> get a whole lot more interesting. This, btw, is independent of whether
> we consider snapshot isolation, read stability or cursor stability.
> (just to point out that this isn't an Oracle vs. DB2 issue)
> Each of these "lesser" isolation levels will allow an application to
> behave dangerous. Column level locking (or pivoting tables) will only
> add another shade to this danger.
> Anyway, it appears I at least start repeating myself and given that no
> vendor supports such a locking level I'll just leave you with this final
> comment.
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> DB2 UDB for Linux, Unix, Windows
> IBM Toronto Lab
Received on Mon Dec 19 2005 - 08:50:56 CST

Original text of this message