Re: Serious article on comparison between MS SQL Server 2005 Yukon and Oracle 10g

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Tue, 23 Nov 2004 08:01:54 GMT
Message-ID: <S7Cod.2830$uV6.2714@newsread3.news.pas.earthlink.net>

DA Morgan wrote:

> Jonathan Leffler wrote:

>> DA Morgan wrote:
>>> Mladen Gogala wrote:
>>>> Finally, the debate that got my eyebrows raised was the discussion
>>>> of the identity field vs. sequences, claiming sequences to be a
>>>> "major overhead".
>>>>
>>>> Well, sequences are certain (but by no means a major one)
>>>> overhead because they allow things like clustering (see "ORDERED"
>>>> and "NOCACHE") as well as good caching for performance. The
>>>> article doesn't say anything about he performance of the identity
>>>> fields in the clustered environment.
>>>
>>> Actually identity columns have more of an impact on performance
>>> than do sequences which is why IBM immediately added sequences to
>>> Informix. The sad fact of identity columns is that they force
>>> serialization.
>>
>> Informix SERIAL columns do not force serialization - for any
>> reasonable meaning of serialization with which I am familiar.
>>
>> Suppose two transactions, TxA and TxB, both insert a record into a
>> table with a serial column. For sake of argument, TxA does so first
>> and is allocated number 1000. TxB is allocated number 1001 (under
>> normal circumstances), regardless of whether TxA has committed or
>> rolled back or is still active. The values are assigned without more
>> than a transitory latch to permit the serial counter value to be
>> updated. Suppose TxA rolls back. TxB may still commit (or
>> rollback). Any subsequent transaction TxC will be allocated 1002,
>> regardless of whether TxA or TxB or both rollback. Of course, either
>> TxA or TxB or both may insert multiple records into the table with the
>> serial column, or into any other tables; there are no petty
>> restrictions for having acquired a new serial number.

> 
> A "transitory" latch is still serialization. That is substantially
> different than a cached sequence.

I beg to differ, but since we seem to be defining the same word (serialization) in different ways, maybe it is best to leave this as "there are different views on this". OTOH, I'll try to explain again what I mean. [Come to think of it, maybe the nomenclature problem is the distinction between a lock and a latch - they are different in IDS. Roughly speaking, a lock is a heavyweight object intended to coordinate access by transactions in separate sessions to parts of the database that are logically stored on disk. A latch is a lightweight concurrency control mechanism to coordinate access to a piece of memory by separate CPUs.]

The SERIAL column is no more serialized than a cached sequence - both generate a new set of values (an IDS SERIAL column generates a set with one value; a cached sequence a set with a configurable number of values) ensuring that no other process interferes with the process of generating that set of values but not otherwise holding up concurrent processes using the same generator. (Or are you going to tell me that Oracle does not ensure that multiple concurrent sessions on a multi-CPU machine all get different sets of cached values and that the same sequence number from a single sequence could, therefore, be issued to multiple sessions?) In IDS, the serial value counter is not locked - it is latched to ensure that only single process accesses it while the value is being generated, but the latch is released as soon as the value has been correctly generated (and the same generator may be used many times by both the same session and by other sessions before the transaction in the first session terminates, whether that's with a COMMIT or ROLLBACK). Serial values are never reissued (barring wraparound to one after the maximum value is generated); once the increment has occurred, it stays incremented.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Received on Tue Nov 23 2004 - 02:01:54 CST