Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: oracle - mysql comparison

Re: oracle - mysql comparison

From: Karl Schendel <>
Date: Wed, 21 Jul 2004 22:47:51 GMT
Message-ID: <>

In article <VAzLc.140028$JR4.45073_at_attbi_s54>,  "Jim Kennedy" <> wrote:

> "Karl Schendel" <> wrote in message
> > It was never true in Ingres that an insert locked the entire index
> > (unless the optimizer decided to take out an initial table level lock,
> > and that would only happen for unqualified updates.)
> > What Jim may have run into is lock contention on the last leaf of
> > a btree index with a sequential key, which would in effect serialize
> > the inserts. For some time now, Ingres has had row level locking
> > available. Of course one could avoid sequential keys, which have
> > other potential performance problems (eg I/O hot spots).
> >
> > I have to say I'm skeptical of how much experience Mr Kennedy might
> > have had with Ingres if he can't spell it properly.
> >
> > Karl
> Karl,
> I suspect I am older than you.

Perhaps not...
In any case, I've used Ingres for 14 years. I can't talk details about any version prior to 6.3/03.

> But an insert into a table (heap type) would
> lock almost the whole table until a commit because the primary key would
> need to be updated.

Ah! When you talked about primary keys, it didn't occur to me that you might have been dealing with a heap table (with secondary index providing the keying). I just assumed that you meant a btree or hash table.

It's true, an insert into a heap takes an "extend" lock on the last page (which is where heaps always insert). So if you have a heap, only one person can insert at a time. The answer is of course "don't do that". Many years ago, heap-with-index was popular with some Ingres database designers -- I have no idea why. I made a nice profit a couple years back, fixing some sites that had that setup.

> (doing an insert not an update) At the time it was a
> page level locking database not a row level locking database. (It may have
> changed since then; I wouldn't be surprised.) It would lock all the pages
> that would be traversed to update the index for the primary key. This is
> usually 95% of the table. That means that most of the time 2 people
> couldn't insert a row into a table at the same time.
> Jim

Since Ingres's default lock strategy is the most restrictive (page serializable), you can still get lock contention in btree leaf pages if you don't do something to spread out your keys. There's lots of knobs for changing the lock strategy, though. Intelligently designed Ingres applications use them.

And by the way, Ingres release 3 (the one that's going open source in a couple weeks) does support range/list/hash partitioned tables, and parallel query execution. I don't pretend feature parity with Oracle and I wouldn't even claim it's a sensible goal.

Karl Received on Wed Jul 21 2004 - 17:47:51 CDT

Original text of this message