Skip site navigation (1) Skip section navigation (2)

Re: incoherent view of serializable transactions

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Gregory Stark" <stark(at)enterprisedb(dot)com>
Cc: "Emmanuel Cecchet" <manu(at)frogthinker(dot)org>,<pgsql-hackers(at)postgresql(dot)org>,"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: incoherent view of serializable transactions
Date: 2008-12-23 17:24:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
>>> Gregory Stark <stark(at)enterprisedb(dot)com> wrote: 
> "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
>> the serializable transaction looks at is locked against updates
>> 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
>> scan is required, the entire table is locked against all
>> modifications.  (That's right, it is not unusual to have entire
>> 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
> locking" or not. It sounds like it locks a whole lot more than just
> 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
>> 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
>> transaction) would NOT provide true serializable behavior?  (Keep
>> mind that that's the broad stroke overview -- the full details
>> 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
> 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.

In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2008-12-23 17:37:03
Subject: Re: Sync Rep: First Thoughts on Code
Previous:From: Fujii MasaoDate: 2008-12-23 17:23:24
Subject: Re: Sync Rep: First Thoughts on Code

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group