Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
in [PgSql]
|
Prev: [HACKERS] nvarchar notation accepted?
Next: pgsql: Add PGFILEDESCdescription to Makefiles for all /contrib
From: Florian Pflug on 13 May 2010 19:02 On May 13, 2010, at 23:51 , Kevin Grittner wrote: > Florian Pflug <fgp(a)phlo.org> wrote: > >> All in all, I believe that SHARE and UPDATE row-level locks should >> be changed to cause concurrent UPDATEs to fail with a >> serialization error. I can come up with a patch that does that, >> but I wanted to get some feedback on the idea before I put the >> work in. > > Before you work on that, you might want to wait until you can review > the work that I and Dan Ports (a Ph.D. candidate from MIT) have been > doing on support for true serializable transactions. You don't need > to use FOR SHARE or FOR UPDATE or any explicit locks as long as the > concurrent transactions are SERIALIZABLE. We have it working, but > have been holding off on discussion or patch submission at Tom's > request -- he felt it would distract from the process of getting the > release out. I'm very exited about the work you're doing there, and believe it'd be a great feature to have. However, I view my proposal as pretty orthogonal to that work. True serializable transaction are much more powerful than what I proposed, but at a much higher price too, due to the necessity of SIREAD locks. My proposal allows for simple FK-like constraints to be implemented at user-level that are correct for all isolation levels. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Kevin Grittner" on 14 May 2010 06:56
[slight rearrangement] Florian Pflug wrote: > I'm very exited about the work you're doing Always nice to hear. :-) > I view my proposal as pretty orthogonal to that work. > My proposal allows for simple FK-like constraints to be > implemented at user-level that are correct for all isolation > levels. OK, I can see the attraction in that. > True serializable transaction are much more powerful than what I > proposed, but at a much higher price too, due to the necessity of > SIREAD locks. I think that SIREAD locks will generally be cheaper than SELECT FOR UPDATE, since the former don't require any disk I/O and the latter do. I only have one benchmark so far (more on the way), but it attempts to isolate the cost of acquiring the SIREAD locks by using a read-only load against a fully cached database. Benchmarks so far show the new version of the SERIALIZABLE level as supporting 1.8% fewer TPS than REPEATABLE READ (the existing snapshot isolation level) in that environment. That will probably disappear into the noise for any load involving disk I/O. Now *rollbacks*, particularly those due to false positives, might become a more serious issue in some pessimal loads, but I'm still working on developing meaningful benchmarks for that. I guess what I'm suggesting is that unless you have a very small database with a very large number of connections in a high contention workload, or you can't require SERIALIZABLE transaction isolation level, SSI might actually perform better than what you're proposing. Of course, that's all conjecture until there are benchmarks; but I'd be very interested in getting any and all alternative solutions like this worked into a benchmark -- where I can pull out the FOR UPDATE and FOR SHARE clauses, any redundant updates or denormalizations added just for concurrency issues, and all explicit locking -- and compare that under SERIALIZABLE to the original performance. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |