Tom Lane <t...@sss.pgh.pa.us> wrote: > The fundamental problem with all the "practical" approaches I've > heard of is that they only work for a subset of possible predicates > (possible WHERE clauses). The idea that you get true > serializability only if your queries are phrased just so is ... > icky. So icky that it doesn't sound like an improvement over what > we have. I've never seen or heard of a production system which only gives you serializable guarantees for some WHERE clauses. What I have always seen is multi-granularity locking, where locks are based on indexes or rows accessed -- essentially letting the DBMS figure out what rows the predicate covers by seeing what it examines. If too many locks accrue at a fine granularity, they are replaced with a lock at a coarser granularity. There have been papers published on the technique for decades, and it has been used in popular databases for almost as long. The only objection, outside of aesthetic ones, raised so far is that we don't know of anyone using this approach with some of the innovative index techniques available in PostgreSQL. I don't believe that means that it can't be done. Well, OK -- there is another objection -- that using this technique creates locking on a less-than-surgically-precise set of data, leading to blocking and/or serialization failures which would not happen with a theoretically ideal implementation of predicate locks. The problem is that the cost of a "perfect" predicate locking system is much higher than the cost of letting some transaction block or roll back for retry. If someone has an approach to predicate locking which retains precision in lock scope without excessive cost, I'm more than willing to use it. Frankly, the fact that someone came up with a way to *use* predicate locks to implement serializable transactions on top of MVCC, without blocking beyond what's already there to support snapshot isolation, has me believing that there could be more surprises around the corner. I do think that it might be best to get an initial implementation using "conventional" locking, and *then* consider the fancier stuff. That would allow an approach which has surgical precision for a subset of WHERE clauses to be used where it can be, with the fall-back being broader (but not ridiculous) conventional locks where the technique can't be used, rather than falling back to failure of serializable behavior. -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
