Friday, November 03, 2006

Interesting Post...

Kathy Sierra rarely disappoints with her postings and this one is no exception.  I really liked this quote:

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. 

Things change, all of the time.  One example I use to talk about this is the "case of the unindexed foreign key".

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:


create table p ( x int primary key );
create table c ( x references p );
insert into p values (1);
insert into c values (1);
update p set x = x;


insert into c values ( 1 );

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:


create table p ( x int primary key );
create table c ( x references p );
insert into p values (1);
insert into p values (2);
insert into c values (1);


insert into c values ( 1 );

Session1 again:

delete from p where x = 2;

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.



Anonymous Martin said....

Excuse me, though I'm not native english I think you say Kathy almost always disappoints with her postings? Sure you do not mean it like that. :-D

Fri Nov 03, 10:07:00 AM EST  

Anonymous RobH said....

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.

Fri Nov 03, 10:09:00 AM EST  

Blogger Thomas Kyte said....

Sure you do not mean it like that.

Oh man, messed that up, thanks! definitely got the meaning backwards.

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:

where decode(x,null,1,0) = 1


I can prove that premise is false in at least some cases.

Case: concatenated index on columns X,Y. Y is not null.

ops$tkyte%ORA10GR2> create table t ( x int, y int NOT NULL, z int );

Table created.

ops$tkyte%ORA10GR2> insert into t
2 select decode(mod(rownum,100),0,null,rownum), rownum, rownum
3 from all_objects;

50213 rows created.

ops$tkyte%ORA10GR2> create index t_idx on t(x,y);

Index created.

ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats(user,'T')

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t where x is null;

Execution Plan
Plan hash value: 470836197

| Id | Operation | Name | Rows | Bytes | Cost (%CPU
| 0 | SELECT STATEMENT | | 502 | 7028 | 6 (0
| 1 | TABLE ACCESS BY INDEX ROWID| T | 502 | 7028 | 6 (0
|* 2 | INDEX RANGE SCAN | T_IDX | 502 | | 3 (0

Predicate Information (identified by operation id):

2 - access("X" IS NULL)

ops$tkyte%ORA10GR2> set autotrace off

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)

Fri Nov 03, 10:25:00 AM EST  

Blogger Peter K said....

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.

Fri Nov 03, 10:59:00 AM EST  

Blogger Thomas Kyte said....

It's a constant learning and re-learning.

that is the only reason I've stayed here over 13 years now :)

It would be utterly boring any other way.

Fri Nov 03, 11:11:00 AM EST  

Anonymous Anonymous said....


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.

Fri Nov 03, 11:57:00 AM EST  

Blogger Thomas Kyte said....

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

Fri Nov 03, 12:02:00 PM EST  

Anonymous RobH said....

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.

1. Question something (curiosity)
2. Predict (intuition)
3. Observe (this is KEY)
4. Analyze the outcome
5. Interpret results (state in your own words what just happened)
6. See #1

Fri Nov 03, 12:16:00 PM EST  

Anonymous robert said....

Tom said...
We have to have a different test to see the locking....

I'm confused now because it seems all "flipped around"

So Tom, why is "insert into c" causing a full lock on "p" ???
Please explain why Oracle has to full-lock "p", thanks

Fri Nov 03, 12:20:00 PM EST  

Anonymous Robert said....

BTW, I was wanting to read the FAQ part of Ch.11 (Indexes) of your "EO DB Architecture" and what I found was
posted here

wanna see a pic ? ;)

Fri Nov 03, 12:35:00 PM EST  

Anonymous RobH said....

I had the same issue, but its fixed now.

Fri Nov 03, 02:06:00 PM EST  

Blogger Thomas Kyte said....

I'm confused now because it seems all "flipped around"

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.

Fri Nov 03, 02:36:00 PM EST  

Blogger Noons said....

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

Now, THAT is interesting!

Sun Nov 05, 04:42:00 AM EST  

Blogger Gary Myers said....

"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.]

Mon Nov 06, 11:32:00 PM EST  

Anonymous Anonymous said....

Is there an email address at oracle for suggesting new features?

Thu Nov 09, 12:17:00 AM EST  

Anonymous Anonymous said....

I ran the sql mentioned (on Oracle 10g)

In session 2 I get the deadlock

In session 1 I was able to delete the record successfully

Is this what was expected?

Fri Apr 17, 06:50:00 AM EDT  

Blogger Thomas Kyte said....


there are TWO snippets of sql mentioned. You do not say which you were testing with..

But, that asided -

Session 2 would not be deadlocked, it would be BLOCKED - please clarify

Fri Apr 17, 07:15:00 AM EDT  

Anonymous Anonymous said....

Hi Tom,
My sincere apologies!
I re ran the script and found that i did not run the commit in session 1 hence the deadlock in session2!
Thanks again

Fri Apr 17, 11:28:00 AM EDT  


<< Home