Re: commit

In article <VsjM6.7768$p33.164070_at_news1.sttls1.wa.home.com>,  "Jim Kennedy" <kennedy-family_at_home.com> wrote:

> Ingres does have transactions, but it in a way forces you to use auto
> commit(which means that you aren't using transactions). The concurrency
> model in Ingres (assuming it hasn't changed since I last used it) is brain
> dead. For example, if you insert a row into a table in Ingres with a
> primary key then most users will not be able to do anything with that table
> until you issue a commit! Why? Well Ingres is a page level locking
> database and so it locks all the pages that the primary key index traverses
> to add the row you are inserting. Which is usually about 95% of the table.
> Stupid I know, but that is how it works.

This is not true. Ingres does do locking, but it only locks the leaf and data pages needed to access the rows you want. (We're assuming btree here, Ingres also supports hash-structured tables.) This is most certainly NOT 95% of the table. There is one situation where the locking is a pain, which is when have a monotonic key and a tree-structured table; concurrent inserts will tend to hit the same btree leaf and interfere with one another. The standard cure is to not use a monotonic key, or to use HASH, or to get your transactions over with quickly. (which does not mean AUTOCOMMIT!) Ingres II, which has been out for about 3 years now, allows row level locking if you ask for it.

And why does Ingres lock at all, you ask? So that it can implement the SERIALIZABLE concurrency condition which the SQL standard says should be the default access mode. You can modify the locking scheme, or turn it off entirely, depending on your needs. The default is SERIALIZABLE since it's the safest and should be used if you don't know what you are doing.

Karl Received on Wed May 16 2001 - 11:03:53 CDT