Re: incoherent view of serializable transactions

Reading the spec it does seem our initial statement "The SQL standard defines
four levels of transaction isolation in terms of three phenomena that must be
prevented between concurrent transactions" is not accurate.

The spec defines the first three modes in terms of P1,P2,P3 but serializable
is defined, as Kevin describes, as literally serializable. It is included in
the table but with the note:

NOTE 71 — The exclusion of these phenomena for SQL-transactions executing at
isolation level SERIALIZABLE is a consequence of the requirement that such
transactions be serializable.

So it's clear that that's not the definition. Excluding P1,P2,P3 is necessary
but not sufficient to meet the spec's definition of Serializable.

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:

>>>> Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>> Afaict doing a few google searches Sybase doesn't do predicate locking >
>> either.
>
> The page locking provides this because every index page or data page
> the serializable transaction looks at is locked against updates until
> the end of the transaction. If it can see all the COLUMN=0 rows
> through an index, the index locks protect the transaction. If a table
> scan is required, the entire table is locked against all
> modifications. (That's right, it is not unusual to have entire tables
> locked against any modification until the end of a database
> transaction.)

Ah, so they don't actually use the term predicate locking which is why my
google-fu was inadequate. I'm not sure if this is technically "predicate
locking" or not. It sounds like it locks a whole lot more than just the
predicate.

> All the examples provided in this thread would be handled by Sybase
> with proper serializable semantics. When I proposed changing the docs
> to omit the reference to our lack of knowledge about other database
> products, there was a full example of code that didn't serialize
> according to the mathematical definition. I cut and pasted into
> Sybase and provided the results -- a deadlock.
>
> Can you provide any example or logical explanation of where the
> technique I outline above (locking against modification for every
> index and data page read during the transaction until the end of the
> transaction) would NOT provide true serializable behavior? (Keep in
> mind that that's the broad stroke overview -- the full details include
> various lock escalation techniques, etc.)

I imagine they preemptively escalate to locking the table if you're going to
do a sequential scan? Otherwise an inserter might insert on a page you haven't
read yet (and therefore haven't locked yet)?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!