Re: Overhead cost of Serializable Snapshot Isolation

On Tue, Oct 11, 2011 at 1:11 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> On Mon, Oct 10, 2011 at 11:31 PM, Kevin Grittner
> <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> Simon Riggs <simon(at)2ndQuadrant(dot)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.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company