Re: Notes on lock table spilling - Mailing list pgsql-hackers

On Wed, 2005-03-30 at 18:09 -0400, Alvaro Herrera wrote:
> I'm seeing what can I do about spilling the lock table to disk
> I welcome comments on the ideas outlined here.  If anyone sees a
> showstopper please let me know.

Perhaps a little delayed, but yes, I have major reservations about the
whole concept of spilling the lock table to disk. If you implement this,
I would very much like a switch to be able to turn it off, somehow.

All else you've done about locking sounds great and I don't want my
comments here to detract from my general appreciation of your work and
the project as a whole.

IIRC there is not another major system that spills locks to disk and
there's a big reason: performance is very poor. Other systems accept
some limitations in order to avoid that. Oracle takes locks and holds
them within the block itself, which then need to have block cleanup
performed on them next time they're read. DB2 has a lock table which
doesn't grow in size or spill to disk, ever. [It does do lock
escalation, which isn't too great either - but this will only be granted
if no other users conflict, so its not too bad a solution.]

DB2 even goes to great lengths to avoid this by offering additional
locking modes of Cursor Stability (CS) - which only locks the rows
currently being viewed or on which a cursor is currently placed. DB2
would call locking everything Repeatable Read mode (RR) which performs
so badly in most situations people don't use it - and thats even without
spilling to disk. Oracle limits the number of blocks that will have
copies of them held in the Undo tablespace, so there is an effective
limit on number of locks that can be taken there also.

I would be more inclined to implement a rolling window lock similar to
CS, so you never run out of lock table space. That can give you issues,
sure, but not performance ones. That idea may have flaws also: I do not
wish at this stage to back an alternative, just to say that the spilling
to disk idea sounds like it has big problems, IMHO.

If you did go down the route of spilling to disk, I'd would like to make
very sure that only the person that asked for hundreds of locks would be
effected by all those locks. i.e. only their locks were spilled to disk,
so the lock cache is effectively "scan resistant". Other users with
modest locking requests would be uneffected. Not sure how you'd do that
with the SLRU code as it is.

There are no easy answers to this conundrum, but I'm not sure spilling
to disk is the answer. 

Best Regards, Simon Riggs