Re: Serializable Snapshot Isolation
Serializable Snapshot Isolation
От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
Attached is the latest Serializable Snapshot Isolation (SSI) patch. With Joe's testing and review, and with stress tests adapted from those used by Florian for his patch, we were able to identify and fix several bugs. Stability seems good now. We have many tests for correct behavior which are all looking good. The only solid benchmarks we have so far show no impact on isolation levels other than SERIALIZABLE, and a 1.8% increase in run time for a saturation run of small, read only SERIALIZABLE transactions against a fully cached database. Dan has been working on setting up some benchmarks using DBT-2, but doesn't yet have results to publish. If we can get more eyes on the code during this CF, I'm hoping we can get this patch committed this round. This patch is basically an implementation of the techniques described in the 2008 paper by Cahill et al, and which was further developed in Cahill's 2009 PhD thesis. Techniques needed to be adapted somewhat because of differences between PostgreSQL and the two databases used for prototype implementations for those papers (Oracle Berkeley DB and InnoDB), and there are a few original ideas from Dan and myself used to optimize the implementation. One reason for hoping that this patch gets committed in this CF is that it will leave time to try out some other, more speculative optimizations before release. Documentation is not included in this patch; I plan on submitting that to a later CF as a separate patch. Changes should be almost entirely within the Concurrency Control chapter. The current patch has one new GUC which (if kept) will need to be documented, and one of the potential optimizations could involve adding a new transaction property which would then need documentation. The premise of the patch is simple: that snapshot isolation comes so close to supporting fully serializable transactions that S2PL is not necessary -- the database engine can watch for rw-dependencies among transactions, without introducing any blocking, and roll back transactions as required to prevent serialization anomalies. This eliminates the need for using the SELECT FOR SHARE or SELECT FOR UPDATE clauses, the need for explicit locking, and the need for additional updates to introduce conflict points. While block-level locking is included in this patch for btree and GiST indexes, an index relation lock is still used for predicate locks when a search is made through a GIN or hash index. These additional index types can be implemented separately. Dan is looking at bringing btree indexes to finer granularity, but wants to have good benchmarks first, to confirm that the net impact is a gain in performance. Most of the work is in the new predicate.h and predicate.c files, which total 2,599 lines, over 39% of which are comment lines. There are 1626 lines in the new pg_dtester.py.in files, which uses Markus Wanner's dtester software to implement a large number of correctness tests. We added 79 lines to lockfuncs.c to include the new SIReadLock entries in the pg_locks view. The rest of the patch affects 286 lines (counting an updated line twice) across 25 existing PostgreSQL source files to implement the actual feature. The code organization and naming issues mentioned here remain: http://archives.postgresql.org/pgsql-hackers/2010-07/msg00383.php -Kevin
Re: Serializable Snapshot Isolation
От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
I wrote: > Heikki Linnakangas wrote: > >> ISTM you never search the SerializableXactHash table using a hash >> key, except the one call in CheckForSerializableConflictOut, but >> there you already have a pointer to the SERIALIZABLEXACT struct. >> You only re-find it to make sure it hasn't gone away while you >> trade the shared lock for an exclusive one. If we find another >> way to ensure that, ISTM we don't need SerializableXactHash at >> all. >> it seems like it could be made simpler somehow.. > > After tossing it around in my head for a bit, the only thing that > I see (so far) which might work is to maintain a *list* of > SERIALIZABLEXACT objects in memory rather than a using a hash > table. The recheck after releasing the shared lock and acquiring > an exclusive lock would then go through SerializableXidHash. After discussion on a separate thread, I replaced that hash table with a home-grown shared memory list. I had to create a patch at that point due to the git migration, so I figured I might as well post it, too. There have been some non-trivial changes due to feedback on the prior posting. -Kevin
Re: Serializable Snapshot Isolation
От:
Greg Stark <gsstark@mit.edu>
Дата:
Just to be clear I wasn't saying it was or wasn't a problem, I was just trying to see if I understand the problem and if I do maybe help bring others up to speed.
On 25 Sep 2010 23:28, "Kevin Grittner" <Kevin.Grittner@wicourts.gov> wrote:
> Greg Stark wrote:
>
>> So T1 must have happened before TN because it wrote something based
>> on data as it was before TN modified it. But T0 can see TN but not
>> T1 so there's no complete ordering between the three transactions
>> that makes them all make sense.
>
> Correct.
>
>> The thing is that the database state is reasonable, the database
>> state is after it would be if the ordering were T1,TN with T0
>> happening any time. And the backup state is reasonable, it's as if
>> it occurred after TN and before T1. They just don't agree.
>
> I agree that the database state eventually "settles" into a valid
> long-term condition in this particular example. The point you are
> conceding seems to be that the image captured by pg_dump is not
> consistent with that. If so, I agree. You don't see that as a
> problem; I do. I'm not sure where we go from there. Certainly that
> is better than making pg_dump vulnerable to serialization failure --
> if we don't implement the SERIALIZABLE READ ONLY DEFERRABLE
> transactions I was describing, we can change pg_dump to use
> REPEATABLE READ and we will be no worse off than we are now.
>
> The new feature I was proposing was that we create a SERIALIZABLE
> READ ONLY DEFERRABLE transaction style which would, rather than
> acquiring predicate locks and watching for conflicts, potentially
> wait until it could acquire a snapshot which was guaranteed to be
> conflict-free. In the example discussed on this thread, if we
> changed pg_dump to use such a mode, when it went to acquire a
> snapshot it would see that it overlapped T1, which was not READ ONLY,
> which in turn overlapped TN, which had written to a table and
> committed. It would then block until completion of the T1
> transaction and adjust its snapshot to make that transaction visible.
> You would now have a backup entirely consistent with the long-term
> state of the database, with no risk of serialization failure and no
> bloating of the predicate lock structures.
>
> The only down side is that there could be blocking when such a
> transaction acquires its snapshot. That seems a reasonable price to
> pay for backup integrity. Obviously, if we had such a mode, it would
> be trivial to add a switch to the pg_dump command line which would
> let the user choose between guaranteed dump integrity and guaranteed
> lack of blocking at the start of the dump.
>
> -Kevin
> Greg Stark wrote:
>
>> So T1 must have happened before TN because it wrote something based
>> on data as it was before TN modified it. But T0 can see TN but not
>> T1 so there's no complete ordering between the three transactions
>> that makes them all make sense.
>
> Correct.
>
>> The thing is that the database state is reasonable, the database
>> state is after it would be if the ordering were T1,TN with T0
>> happening any time. And the backup state is reasonable, it's as if
>> it occurred after TN and before T1. They just don't agree.
>
> I agree that the database state eventually "settles" into a valid
> long-term condition in this particular example. The point you are
> conceding seems to be that the image captured by pg_dump is not
> consistent with that. If so, I agree. You don't see that as a
> problem; I do. I'm not sure where we go from there. Certainly that
> is better than making pg_dump vulnerable to serialization failure --
> if we don't implement the SERIALIZABLE READ ONLY DEFERRABLE
> transactions I was describing, we can change pg_dump to use
> REPEATABLE READ and we will be no worse off than we are now.
>
> The new feature I was proposing was that we create a SERIALIZABLE
> READ ONLY DEFERRABLE transaction style which would, rather than
> acquiring predicate locks and watching for conflicts, potentially
> wait until it could acquire a snapshot which was guaranteed to be
> conflict-free. In the example discussed on this thread, if we
> changed pg_dump to use such a mode, when it went to acquire a
> snapshot it would see that it overlapped T1, which was not READ ONLY,
> which in turn overlapped TN, which had written to a table and
> committed. It would then block until completion of the T1
> transaction and adjust its snapshot to make that transaction visible.
> You would now have a backup entirely consistent with the long-term
> state of the database, with no risk of serialization failure and no
> bloating of the predicate lock structures.
>
> The only down side is that there could be blocking when such a
> transaction acquires its snapshot. That seems a reasonable price to
> pay for backup integrity. Obviously, if we had such a mode, it would
> be trivial to add a switch to the pg_dump command line which would
> let the user choose between guaranteed dump integrity and guaranteed
> lack of blocking at the start of the dump.
>
> -Kevin
