Re: Overhead cost of Serializable Snapshot Isolation

On Tue, Oct 11, 2011 at 6:14 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Tue, Oct 11, 2011 at 1:11 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> On Mon, Oct 10, 2011 at 11:31 PM, Kevin Grittner
>> <Kevin.Grittner@wicourts.gov> wrote:
>>> Simon Riggs <simon@2ndQuadrant.com> wrote:
>>>
>>>> How do we turn it on/off to allow the overhead to be measured?
>>>
>>> User REPEATABLE READ transactions or SERIALIZABLE transactions.  The
>>> easiest way, if you're doing it for all transactions (which I
>>> recommend) is to set default_transaction_isolation.
>>
>> Most apps use mixed mode serializable/repeatable read and therefore
>> can't be changed by simple parameter. Rewriting the application isn't
>> a sensible solution.
>>
>> I think it's clear that SSI should have had and still needs an "off
>> switch" for cases that cause performance problems.
>
> Is it possible that you are confusing the default level, which is READ
> COMMITTED, with REPEATABLE READ?  I can't see why anyone would code up
> their application to use REPEATABLE READ for some things and
> SERIALIZABLE for other things unless they were explicitly trying to
> turn SSI off for a subset of their transactions.  In all releases
> prior to 9.0, REPEATABLE READ and SERIALIZABLE behaved identically, so
> there wouldn't be any reason for a legacy app to mix-and-match between
> the two.

Yes, I mistyped "read" when I meant "committed". You are right to
point out there is no problem if people were using repeatable read and
serializable.

Let me retype, so there is no confusion:

It's common to find applications that have some transactions
explicitly coded to use SERIALIZABLE mode, while the rest are in the
default mode READ COMMITTED. So common that TPC-E benchmark has been
written as a representation of such workloads. The reason this is
common is that some transactions require SERIALIZABLE as a "fix" for
transaction problems.

If you alter the default_transaction_isolation then you will break
applications like this, so it is not a valid way to turn off SSI.

-- 
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services