Hey guys! Our repeatable read is actually serializable -
even though it can't be serialized!

-------- Original Message --------
Subject: Re: ANSI Isolation Level vs. InnoDB consistent read implementation
Date: Thu, 09 Jul 2009 15:59:23 -0700
From: ken.jacobs@... <ken.jacobs@...>
To: Harrison Fisk <Harrison.Fisk@...>
CC: Sergei Golubchik <serg@...>, Xuekun Hu <xuekun.hu@...>,
Internals List <internals@...>, Peter Gulutzan
<peterg@...>
References:
<398d69300907070632j619283eaw7962c583b874dcda@...>
<398d69300907071739j467a9cabl6435332f0ed9e884@...>
<4A54724A.9090806@...> <4A549393.1030705@...>
<398d69300907080646n155fb63fhdd430d76a019da97@...>
<B4CC5BAA-9A4A-4151-B918-F1FEF398D001@...>
<20090708162916.GA1739@...>
<FEC1AEE6-DFC3-473B-ACC4-06DA569D56A0@...>

As a former member (7.5 years) of the SQL committte, including when
this part of the Standard was written , I guess I should comment.
Also, I was involved in the design of the Oracle serializable mode,
and we do believe we comply with the Standard.

The "normative" part of the Standard is descriptive, but not
necessarily precise. Only the syntax and general rules in the body
of the standard can be used as a yardstick. You can't interpret the
English text in any way that differs from the detailed specifications.

So, as long as a product prevents a transaction from seeing prohibited
phenomena, it conforms. Therefore it is correct to say that the
Standard defines serializable in a way that is less strict than a
mathematical definition.

Hope this helps.

Regards,

Ken

Sent from my iPhone

On Jul 8, 2009, at 10:10 AM, Harrison Fisk <Harrison.Fisk@...>
wrote:

> Hi Serg,
>
> On Jul 8, 2009, at 12:29 PM, Sergei Golubchik wrote:
>
>> Hi, Harrison!
>>
>> On Jul 08, Harrison Fisk wrote:
>>> On Jul 8, 2009, at 9:46 AM, Xuekun Hu wrote:
>>>
>>> One additional thing I want to point out is that while the standard
>>> defines SERIALIZABLE according to the three phenomena that it
>>> prevents, InnoDB SERIALIZABLE is actually mathematically
>>> serializable
>>> whereas just disallowing the three phenomena isn't necessarily so.
>>
>> Not exactly. The standard defines SERIALIZABLE as, well, being
>> serializable. Other isolation levels are defines in terms of
>> phenomena,
>> but not SERIALIZABLE.
>>
>> SQL2003, part 2, 4.35.4 Isolation levels of SQL-transactions:
>>
>> The execution of concurrent SQL-transactions at isolation
>> level SERIALIZABLE is guaranteed to be serializable. A
>> serializable execution is defined to be an execution of the
>> operations of concurrently executing SQL-transactions that
>> produces the same effect as some serial execution of those
>> same SQL-transactions. A serial execution is one in which
>> each SQL-transaction executes to completion before the next
>> SQL-transaction begins.
>
>
> Hrm, interesting. I wonder if Peter G was referencing an earlier
> version of the SQL spec (perhaps 99?) when he wrote the following in
> the article:
>
> http://web.archive.org/web/20070409074319/http://www.dbazine.com/db2/db2-disarticles/gulutzan6
>
> ==
> There is a curious situation here. The SQL Standard says that the
> SERIALIZABLE isolation level must be truly serializable — that is, n
> o phenomena of any kind are permissible. But for the SET TRANSACTION
> ISOLATION LEVEL SERIALIZABLE statement, the Standard says specifica
> lly that only the three phenomena (Dirty Read, Non-repeatable Read,
> Phantom) are not permissible. By implication, other phenomena are pe
> rmissible. I have concluded that a multi-versioning DBMS conforms to
> the Standard’s requirements (as Oracle claims to do) even if not al
> l transactions are SERIALIZABLE in the ANSI/ISO SQL sense of the word.
> ==
>
> Regards,
>
> Harrison
> --
> Harrison C. Fisk, MySQL Staff Support Engineer
> MySQL @ Sun Microsystems, Inc., http://www.sun.com/mysql/
>
>
>
>
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe: http://lists.mysql.com/internals?unsub=ken.jacobs@...
>

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?unsub=ann@...