Re: Serializable Isolation without blocking - Mailing list pgsql-hackers

Hi,

Greg Stark wrote:
> aaah... I think I see where we've gone off track in previous
> discussions...you think postgres keeps row level locks in a shared
> memory data structure.  It doesn't it stores all row level locks *in*
> the tuple itself.  It only stores the lock in memory briefly while
> actually acquiring the lock. Once it acquires it the only record of
> the lock is the xid in the tuple itself.

This is a very good point. However, I'm not clear if Kevin plans to go
down to tuple level locking with granularity of the SIREAD thing. (I
don't like calling it a lock, because it actually isn't. Let's better
call it a hint or a mark).

We certainly cannot store all of the SIREAD hint information within the
tuple header, as there may be multiple transactions having marked the
same tuple SIREAD, but we don't want to spend lots of bits for SSI there
(rather no single bit).

It's easy to see that the SIREAD hint information doesn't fit in shared
memory for large enough tables. The only way I can imagine tuple-level
SIREAD locking half-ways working is by using that for transactions
reading only a few tuples and fall back to some coarser grained locking
strategy after a certain limit (per transaction).

> storing the lock data in the tuples won't work for you at all because
> you need to lock rows that don't exist yet at all.

I'm not sure if I understand this correctly, but I don't think you need
to "lock" tuples that don't exist, at least not with SIREAD. The Cahill
paper covers this under "Detecting Phantoms" and proposes to use plain
predicate locking to cover tuple level granularity AFAIUI.

The proposed SIREAD hint seems to be an optimization that only works for
existing tuples. I don't find it hard to believe that it performs better
than a general purpose predicate locking strategy. (Especially for test
cases with short transactions, i.e. only few SIREAD locks per txn).

(It's interesting that with "database page" level granularity, he states
that predicate locking would not be necessary. Instead any page can be
locked at any time. For this to work, according to my reasoning, you'd
have to know in advance on which page potentially accessible (but not
yet visible) new tuples would end up. This is close to impossible for
Postgres, however, it seems to work for Berkeley DB).

> that's why "where to
> store the lock" is a critical blocking issue to figure out to know
> whether the plan is feasible at all.

I absolutely agree to that. As I came to think of it more as a hint or
mark (and because of the different lifecycle of SIREAD hints than
locks), I think basing that on existing table level locks isn't a good idea.

How about storing the SIREAD info in shared memory and using dynamic
granularity based on the conflict rate and available memory? *duck*

As this seems to be an optimization of predicate locking, don't we need
to implement that first?

Regards

Markus Wanner