Re: incoherent view of serializable transactions - Mailing list pgsql-hackers

>>> Gregory Stark <stark@enterprisedb.com> wrote: 
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>>>>> Emmanuel Cecchet <manu@frogthinker.org> 12/23/08 8:59 AM >>> 
>>> Have you ever used serializable transactions with Sybase?
>>  
>> Every day for over 15 years.
> 
> Afaict doing a few google searches Sybase doesn't do predicate
locking 
> either.
> It would very much surprise me if they did since they've always had
the most
> primitive locking infrastructure of the three major databases.
Locking 
> records
> or pages isn't going to provide true standards-compliant
serializable
> transactions in the way you're describing.
> 
> Predicate locking means being able to lock records which don't
actually 
> exist
> yet. Ie, locking all records "WHERE COLUMN=0" even if there are no
such
> records. This has to block any other transaction from inserting such
a 
> record
> or updating another record to set COLUMN to 0.
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.)
>>> Oracle also provides SI like Postgres and I don't think they are
doing that
>>> bad.
>>  
>> I don't quire understand.  Could you clarify?
> 
> The point is Oracle doesn't provide this kind of true serializable
isolation
> and people still find it useful.
Sure, and I find PostgreSQL useful.  I'm not proposing to change it.
> In fact Sybase and DB2 also don't provide
> true serializable transactions -- nobody does. It's a fantasy.
They do.  They have for over 15 years.  If people will read it, I'll
try to find the a web page where they give all the details of the
strategy.
>> There really are good reasons.  I'm not up to going through that
now,
>> but if there is genuine interest in the topic perhaps I can follow
up
>> later.
> 
> I suppose I'm curious whether you're mistaken and your app isn't safe
on
> Sybase because it's depending on truly serializable transactions and
Sybase
> isn't doing that, or if you have examples of transactions which
Sybase
> provides proper serialized semantics for but Postgres doesn't.
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.)
>>> But I am probably missing the point which was to fix the doc?
> 
> But missing the point and having pointless arguments is so much more
fun 
> than
> documentation writing :)
Frankly, I prefer other sports.   :-(
-Kevin