Re: Indexes and Logical design

> "David Cressey" <david.cressey@xxxxxxxxxxxxx> wrote in message
> news:St2Ve.10432$9i4.2220@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> [...]
>>> > Not true. In DEC Rdb/VMS a unique constraint can be declared without
>>> > creating an index, if you want to.
>>>
>
> It's unclear to what part of my first response to your original message "Not
> true" refers.
>
> Earlier, I wrote this: "One can easily imagine a unique constraint
> enforcement without any index whatsoever although such enforcement would be
> impractical". I did not claim that no database could implement a unique
> constraint without an index did I.
>
> It's interesting to note that by mentioning Rgb's ability to create a unique
> constraint without an index you actually reinforce my argument that the
> index is just a performance tool.

Indeed.

There are two "possibly unexpected" ways to implement "UNIQUE":

1. Don't bother with any extra data structure; this would, of course,
mean some form of sequential scan across the whole thing to verify
uniqueness.

2. Use a hash table to allow access to values. This provides O(1)
access time, but with the demerit that this structure is unordered,
and therefore not usable for any other purposes...

>>> For toy tables probably. In 'real world', no.
>>
>> In the real world, yes.
>
> It appears that in the "real world" you model the tables you want to have
> unique constraints on are either small, or you do not care much about
> concurrency when accessing such tables, or both. As is well known,
> accessing a table without an index will lead to a full table scan thereby
> impacting performance if the table is larger than a couple dozen
> rows.

Right.

> Besides, sequential retrieval for a read/write transaction requires locks on
> the entire table, which results in coarser locks and degraded concurrency.

Not necessarily; alternative mechanisms exist, and are actively used.

Pretty well any of the database systems still undergoing active
development offer some variation of MultiVersion Concurrency Control
(MVCC) where updates lead to creating new versions of tuples, which
allows the reads, at least, to not require any locks...
--
(reverse (concatenate 'string "moc.liamg" "@" "enworbbc"))
http://cbbrowne.com/info/
If we were meant to fly, we wouldn't keep losing our luggage.
.

Relevant Pages

  • Urgent: Concurrency in SQL Server 2005: How to use granularity of Locks in Snapshot isolation level
    ... I have developed a VS 2005 database application which has the concurrency ... Locking granularity (database, table, [age, row, ... Versioning (snapshot isolation, read committed ... I would like to use the granularity of locks (rows or index ...
    (microsoft.public.sqlserver.clients)
  • Concurrency in a multi-user environment
    ... concurrency in a multi-user PHP/MySQL environment. ... does locking a record prevent file locks (when new ... numeric field in the table. ...
    (php.general)
  • Re: Serious concurrency problems on fast systems
    ... I worked on a big Java Enterprise project a while ago that had highly concurrent deployment but made quite a number of concurrency mistakes that hugely slowed it down. ... Aside from the fact that the JVM optimizes lock acquisition in the uncontended case, once a thread blocks on a monitor, all the other threads also trying to acquire that monitor also block. ... On that big project we proved this with various enterprise monitoring products that reported on locks, wait times for locks and other performance issues. ... We did three things on that project to improve concurrency: eliminated shared data, made shared data immutable, and used 'java.util.concurrent' classes. ...
    (comp.lang.java.programmer)
  • Re: Locking of nodes in tree for concurrent access
    ... Some item is categorized by N criterias (a criteria is the value ... number of concurrent accesses of the tree), ... For a good understanding of Java concurrency in practice, ... Java 6 has much more efficient handling of locks than earlier versions. ...
    (comp.lang.java.programmer)
  • Re: How to update multiple rows atomically
    ... As has been said before numerous times, shared state concurrency ... It was really only a matter of re-thinking the schemas for a few high-frequency 'tables' and using very selective locking for the high-frequency transactions that the back-office ones were competing with. ... The sequence of user actions is often pre-ordained, implying that certain locks make other locks redundant. ... No experience with so-called concurrent languages nor massive internet systems but I imagine the basic techniques are similar, eg., think of an app that has both high-use and intensive functions as two apps, imagine separate schemas and then make a combination that marries them. ...
    (comp.databases.theory)