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

>>> Gregory Stark <stark@enterprisedb.com> wrote: 
> "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>>>>> Gregory Stark <stark@enterprisedb.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.
Well, I'm not sure whether it is or not; it's a matter of definition. 
If predicate locking is required for true serializable transactions,
and this provides true serializable transactions, it must be, eh? 
Also, an argument could be made that if it locks every page which must
be viewed for execution based on the search predicates, it is doing
predicate locking -- if only indirectly.
>> 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)?
I believe they do go straight to the table lock for a table scan, but
it isn't necessary for full semantics that the transaction lock all
pages in advance.  For most purposes the serializable transaction can
proceed to lock pages as it gets to them.  It will block or deadlock
if a conflict arises.  The transaction may serialize behind a
transaction which started later and read some page it hadn't gotten to
yet, but that doesn't violate the spec or cause any anomalies.  The
key phrase in the spec here is "produces the same effect as *some*
serial execution" [emphasis added].
It will escalate from page locks to a table lock if a (configurable)
number or percentage of a table's pages get locked.
-Kevin