Friday, November 03, 2006

Interesting Post...

Where we used to prepare students for a "job for life", now we must prepare students to be jobless. We must prepare them to think fast, learn faster, and unlearn even faster ("yes, that drug was the appropriate way to treat the XYZ disease, but that was so last week. THIS week we now realize it'll kill you.")

"but that was so last week". I really liked that bit. At OOW I did a keynote entitled "Things you think you know". It is about how our preconceptions - prior (dated) experiences preclude us from doing things right sometimes. The knowledge we think we have needs re-adjusting (frequently). I've speculated that the more experience with Oracle you have, the worse off you may be.

Not everyone knows that in Oracle, if you have an unindexed foreign key and you UPDATE the parent tables primary key (bad idea) or DELETE from the parent table (this definitely happens in real life) a full table lock is taken on the child table.

Sounds straightforward - but things change over time.

In version 5.1.5c (my first version of Oracle) foreign key constraints (added word constraints on Nov 5th, of course foreign keys existed - there were no constraints) did not exist, no problem.

In version 6 of Oracle - which introduced the primary/foreign key syntax, having an unindexed foreign key would cause no locking/concurrency issues at all. The reason - in version 6, primary keys and foreign keys were just metadata in the dictionary. We didn't enforce them, they were syntactic sugar. No enforcement, no locking issues.

Starting in version 7.0 however, the integrity constraints were enforced and the rules of the game changed. A full table lock on the child table would be taken for the duration of the transaction if the child table had an unindexed foreign key AND you updated the parent tables primary key OR deleted from the parent table. So, unless you un-learn what you learned in version 6, you ran into some problems (and many people did, I remember hitting this for the first time - thought it had to be a bug, but of course - it was not).

The rules of the game stayed in place for a couple of releases, 7.0, 7.1, 7.2, 7.3, 8.0, 8.1.5, 8.1.6, 8.1.7 - and then they changed. Subtly - but they changed. Many people came to the conclusion that in 9i Release 1 - Oracle stopped taking the full table lock. This was not the case at all. Rather, Oracle changed the duration of this table lock. The child table is still definitely locked - but for the duration of time it takes to UPDATE the parent primary key OR DELETE a row from the parent table. The relevant bit in the documentation regarding this is:

Oracle no longer requires a share lock on unindexed foreign keys when doing an update or delete on the primary key.It still obtains the table-level share lock, but then releases it immediately after obtaining it.If multiple primary keys are update or deleted, the lock is obtained and released once for each row.

Many people glanced at this and read the first sentence, ran a quick test and came to the conclusion that Oracle doesn't lock the child table anymore (did not read in depth). The reason their test "worked" and seemed to show that unindexed foreign keys didn't lock the table anymore was due to the way the test was written which usually went like this:

Now, in prior releases (7.0 through 8.1.7) that last insert in Session2 would have blocked since session 1 took a full table lock on C when the update to P happened and kept the lock until you committed. Now, in 9i, the table lock was held for the duration of the UPDATE statement only! So, Session2 would not block anymore. We have to have a different test to see the locking, something like this:

Session1 is now blocked (as will be any session that now tries to modify C and hasn't already modified C in their transaction!)

Things change...

It ain't the things you don't know that get you in trouble, it is the things you know that just ain't so (or ain't so anymore, or ain't so ALL OF THE TIME - eg: rules of thumb) that get you in trouble.

Tom, have you ever proved your test case by defining your example to prove the test?

It's common in the scientific community, that rather than test and see what happens, the scientist can design the test to specifically prove their theory rather than reviewing the evidence to re-enforce the theory.

have you ever proved your test case by defining your example to prove the test

many times we are designing a test case to "show something is not always true" - those are the most bullet proof. You set up the example (everyone can see your assumptions) and run through the script - and demonstrate something is not always true.

For example, take this widely accepted premise: NULLS are not indexed, "where x is null" will never use an index. You would want to use a function based index and code something like:

That conclusively shows the commonly held premise is "not always true" (and once you understand the circumstances behind it, you can make all of your "where x is null" predicates "index friendly" even if you have ZERO not null columns in your table:

create index t_idx on t(x,0);

would do it!)

But yes, with a test case, you have to be very very very careful about how you design it when trying to show something "is true". You have to be unbiased and most importantly you have to PUBLISH THE ENTIRE THING and let the community at large RIP IT APART.

Jonathan Lewis is excellent at doing that, he has commented many times on my examples - pointing out "oversights" I've made, assumptions that I must have presumed (but did not list) and so on.

That is why I won't accept any printed paper that says "do X" without the reasoning and some evidence that X is actually beneficial and shows the circumstances X was beneficial in (which is basically the set of assumptions used by the person promoting X)

Tom Kyte said...more experience with Oracle you have, the worse off you may be.

I agree with you there and I would add that sentence if you experience with different versions of Oracle where there have been drastic changes. I started off as a DBA with Oracle v3 up to v7 and I can tell you things are so different and old concepts no longer applies. It's a constant learning and re-learning.

Proving that something isn't true is much easier than proving something is - and, indeed, proof my contradiction is perhaps the only reliable a posteriori type of proof there is, outside pure reason (let's not get into philosophy). Proving something is true - well, that's typically induction, which in turn means making an assumption to begin with.

Proving that something isn't true is much easier than proving something is

absolutely, 100% agreed on that point.

However, showing something "has a specific effect given a set of circumstances" is generally useful as well. As long as you understand the effect and how the circumstances might affect that!

Sometimes - by running a test to show something is "not true", you end up showing "something that is true" - eg: my test case above with regards to NULLS and indexes shows the "premise" held by many to be false, but also shows that it is true we can use an index range scan for "where x is null"...

Given my set of assumptions which need be carefully listed or observable (in this case, assumption is a NULLABLE column in a concatenated index with a NOT NULL columns...)

showing something "has a specific effect given a set of circumstances" is generally useful as well.

EXACTLY. Sometimes things are more complex than we expect. So a better understanding of how things work is certain circumstances is appropriate. An increased understanding of something.

"I can get from home to work in 40 miuntes", yes well its only "true" if I leave home at 6:30 AM. If I leave at 7 it takes 60 minutes.

The important thing is to follow up a comment like that with "why". Although as a child the response was "because I said so". In my experience that drove me to "figure it out", we NEED to re-enforce this.

it isn't flipped around, the insert into C is preventing the DML against P from LOCKING TABLE C.

it is still the same issue, you need a different sequence of events to see it.

If anyone has an outstanding transaction against C, no delete against P can happen and no update to P's primary keys can happen - because those two operations require a full table lock on C and will not get it.

The fact they are trying to lock will will further prevent NEW transactions from modifying C as well.

"In version 5.1.5c (my first version of Oracle) foreign keys did not exist, no problem"

Well, yes indeed: from the point of view of the db engine, this was no problem with v5. From the point of view of application and database data and transactional integrity, this WAS indeed a problem! RI might not have been native to Oracle then, but it didn't stop it being used in application code and the problem was certainly there!

In fact, RI was inbuilt into many codasyl db engines before, wich suffered from even worse problems! :-)

But the bit I find indeed surprising is the "in 9i Oracle changed the duration of this lock"! That to me implies there is a way of "unlocking" a locked row other than by commit or rollback.

"That to me implies there is a way of "unlocking" a locked row other than by commit or rollback. "Well rolling back to a savepoint would unlock a row, even the implicit savepoint at the beginning of an INSERT/UPDATE/MERGE/DELETE statement which it needs to rollback to if the statement fails. You could notionally consider it like a ROW LEVEL trigger that initiates a SAVEPOINT, does a LOCK TABLE, then ROLLBACK TO SAVEPOINT. [Except we can't set savepoints in triggers, which is why we should leave this stuff to Oracle.]

About Me

The views expressed are my own and not necessarily those of Oracle and its affiliates. The views and opinions expressed by visitors to this blog are theirs and do not necessarily reflect mine.
I've been using Oracle since 1988. I've been working at Oracle since 1993 (version 7.0). I spend way too much time working on asktom.oracle.com...