Re: Update on true serializable techniques in MVCC

On 18.12.09 17:33 , Nicolas Barbier wrote:
> I would guess that currently, whether deadlocks can be triggered by
> a set of transactions (i.e., sequences of SQL statements) depends on
> the plan only marginally*. E.g., if two plans happen to use the same
> index, rows may always get locked in the same order by "FOR UPDATE",
> thus preventing certain deadlocks; if the plans were those deadlocks
> might become possible.
>
> Therefore, I don't think that it is currently very typical for
> plan-changes to trigger a massive change in the number of deadlocks
> that happen. The new method might change that property.

Hm, I think that's true if you assume that most application issue pretty
complex SELECT statements but only either pretty simple UPDATEs/DELETEs,
or complex ones but only seldomly.

Once you start hitting a table with a lot of concurrent UPDATEs/DELETES
involving joins and subselects, the picture changes considerably I
think. I must admit, however, that it's hard to imagine a real
application that actually does this, though...

But actually, now that I think about this, I fail to see why the
false-positive serialization error the SIREAD-lock approach generates
would depend on the plan. The existance or non-existance of rw
dependencies does *not* depend on whether the read or write *physically*
happens first, only on their logical ordering (T1 read an item that T2
changed, but T2 did not commit before T1 took it's snapshot).

Plus, the way I read the thesis, the false positives of the SIREAD-lock
approach has nothing to do with the SIREAD locks per se. They are
introduced by the approximate way in which circles contained in the
transaction's dependency graph are detected (the inConflict, outConflict
business).

best regards,
Florian Pflug