>>> Gregory Stark <st...@enterprisedb.com> wrote: > "Kevin Grittner" <kevin.gritt...@wicourts.gov> writes: >>>>> Gregory Stark <st...@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
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
