Re: Serializable Isolation without blocking

On Tue, 2009-05-05 at 10:50 -0500, Kevin Grittner wrote:

> "This paper describes a modification to the concurrency control
> algorithm of a database management system that automatically detects
> and prevents snapshot isolation anomalies at runtime for arbitrary
> applications, thus providing serializable isolation. The new algorithm
> preserves the properties that make snapshot isolation attractive,
> including that readers do not block writers and vice versa. An
> implementation and performance study of the algorithm are described,
> showing that the throughput approaches that of snapshot isolation in
> most cases."

I think this is important work in database theory and a good future
direction for us. It's the right thing to keep an eye on developments
and to consider implementation.

We would need to decide whether intention locks were indeed necessary
when we have MVCC also. Other DBMS without visibility may need to be
more restrictive than we would have to be to implement this. Not sure.

It wouldn't be 692 lines of code and even if it were the impact of that
code would be such that it would need to be optional, since it would
differ in definition from an existing SQL Standard isolation mode and it
would have additional performance implications.

If the use is optional, I would currently prefer the existing mechanism
for implementing serialization, which is to serialize access directly
using either a LOCK statement or an exclusive advisory lock. It's clear
that any new-theory solution will cost significantly more as the number
of users increases, at least O(N^2), whereas simply waiting is only
O(N), AFAICS. (Michael et al don't discuss the algorithmic complexity).
So it seems its use would require some thought and care and possibly
further research to uncover areas of applicability in real usage.

So for me, I would say we leave this be until the SQLStandard changes to
recognise the additional mode. I don't see much advantage for us in
breaking the ground on this feature and it will be costly to implement,
so is a good PhD project.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support