Skip site navigation (1) Skip section navigation (2)

Re: Notes on lock table spilling

From: Greg Stark <gsstark(at)mit(dot)edu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Notes on lock table spilling
Date: 2005-04-04 19:02:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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.


In response to

pgsql-hackers by date

Next:From: Simon RiggsDate: 2005-04-04 19:50:30
Subject: Re: _RollbackFunc : dead code?
Previous:From: Merlin MoncureDate: 2005-04-04 18:50:30
Subject: Re: Notes on lock table spilling

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group