Re: Notes on lock table spilling

Simon Riggs <simon(at)2ndquadrant(dot)com> writes:

> 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 think you're mixing up different things. DB2's Cursor Stability vs
Repeatable Read modes are more akin to READ COMMITTED vs SERIALIZABLE.

Postgres doesn't need to lock records at all for simply ensuring consistent
snapshots. The locks he's working on are the ones used to prevent updates to
parent records while an in-progress transaction is inserting or updating a
child record in a table with a foreign key dependence.

That said, it would be interesting to have an option to set on foreign keys to
indicate that no locks are necessary. Perhaps in the form of an option on the
parent table that would prevent any primary keys from ever being deleted.

--
greg