Simon Riggs <si...@2ndquadrant.com> wrote: > It wouldn't be 692 lines of code Agreed. The original implementation was in an MVCC database which already supported full serializability using strict 2 phase locking and used page level locks. Both of these made the implementation simpler than it would be in PostgreSQL. (And that's not even mentioning sub-transactions and distributed transactions!) > and even if it were the impact of that > code would be such that it would need to be optional I was thinking perhaps a GUC to allow "traditional" behavior when SERIALIZABLE is requested versus using snapshot isolation for REPEATABLE READ and this new technique for SERIALIZABLE. Would that be sane? > If the use is optional, I would currently prefer the existing > mechanism for implementing serialization, which is to serialize > access directly using either a LOCK statement or an exclusive > advisory lock. I'm sure many will, particularly where the number of tables is less than 100 and the number of queries which can be run concurrently is only a thousand or two. Picking out the potential conflicts and hand-coding serialization techniques becomes more feasible on a small scale like that. That said, there's a lot less room for mistakes here, once this new technique is implemented and settled in. When I was discussing the receipting and deposit scenario while trying to clarify the documentation of current behavior, I received several suggestions from respected members of this community for how that could be handled with existing techniques which didn't, in fact, correct the problem. That just points out to me how tricky it is to solve on an ad hoc basis, as opposed to a more rigorous technique like the one described in the paper. The only suggested fix which *did* work forced actual serialization of all receipts as well as actual serialization of those with the deposit report query. The beauty of this new technique is that there would not be any blocking in the described scenario, and there would be a rollback with serialization failure if (and only if) there was an attempt to run the deposit report query while a transaction for a receipt on the old date was still pending. I suspect that the concurrency improvements of the new technique over existing safe techniques would allow it to scale well, at least in our environment. > It's clear that any new-theory solution will cost significantly more > as the number of users increases, at least O(N^2), whereas simply > waiting is only O(N), AFAICS. I'm not following your reasoning on the O(N^2). Could you explain why you think it would follow that curve? > So it seems its use would require some thought and care and possibly > further research to uncover areas of applicability in real usage. Care -- of course. Real usage for serializable transactions -- well known already. (Or are you just questioning performance here?) > So for me, I would say we leave this be until the SQLStandard > changes to recognise the additional mode. It already recognizes this mode; it doesn't yet recognize snapshot isolation (more's the pity). > I don't see much advantage for us in breaking the ground on this > feature and it will be costly to > implement, so is a good PhD > project. Apparently it's already been done as a PhD project -- by Michael Cahill, against InnoDB. -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers