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