Re: Update on true serializable techniques in MVCC

2009/12/18 Florian Weimer <fweimer(at)bfk(dot)de>:

> * Florian Pflug:
>
>> On 16.12.09 16:40 , Kevin Grittner wrote:
>>
>>> Nicolas Barbier<nicolas(dot)barbier(at)gmail(dot)com>  wrote:
>>>
>>>> I am not sure whether the serialization failures that it may cause
>>>>  are dependent on the plan used.
>>>
>>> They are.
>>
>> But so are failures due to deadlocks even today, no?
>
> They are detected.  In this context, "serialization failure" means
> that PostgreSQL generates a history which lacks one-copy
> serializability, without reporting any errors.  (In the general case,
> the unique constraint violation which bugs me personally is a
> different beast and does result in an error.)

FYI (hoping to avoid confusion): When I used the term "serialization
failure" above, I surely meant the kind of failures that would be
detected by the new optimistic algorithm.

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.

This instability problem is often seen on DBMSs that use 2PL on
"blocks read" or "rows inspected" as their main concurrency control
mechanism (e.g., MS-SQL). It is mostly not seen on DBMSs that use MVCC
(because no locks are taken that depend on the peculiarities of the
plan; see caveat above at [*]), and would also not occur when one
would use the most literal implementation of predicate locking
(because the locks taken only depend on the SQL statements' conditions
and not on the plan).

Nicolas