Re: Shared row locking

On Mon, 2004-12-20 at 06:34, Jim C. Nasby wrote:
> On Sun, Dec 19, 2004 at 11:35:02PM +0200, Heikki Linnakangas wrote:
> > On Sun, 19 Dec 2004, Tom Lane wrote:
> >
> > >Heikki Linnakangas <hlinnaka(at)iki(dot)fi> writes:
> > >>On Sun, 19 Dec 2004, Alvaro Herrera wrote:
> > >>>This is not useful at all, because the objective of this exercise is to
> > >>>downgrade locks, from exclusive row locking (SELECT ... FOR UPDATE) to
> > >>>shared row locking.
> > >
> > >>Actually it might help in some scenarios. Remember, we're not talking
> > >>about upgrading shared locks to exclusive locks. We're only talking about
> > >>locking more rows than necessary (all rows).
> > >
> > >Nonetheless, it would mean that locks would be taken depending on
> > >implementation-dependent, not-visible-to-the-user considerations.
> > >Shared locks can still cause deadlocks, and so you would have an
> > >unreliable application, which would only be unreliable under load.
> > >
> > >As I said in connection with the other proposal, weird user-visible
> > >semantics should be the last resort not the first.
> >
> > I agree that lock escalation is not a good solution, we run into problems
> > with DB2 lock escalation at work all the time.
>
> Does anyone know how Oracle deals with this? They use MVCC like
> PostgreSQL, so they'd be a better source for inspiration.

Oracle only uses MVCC in its widest sense - versioning info is stored in
UNDO tablespaces (rollback segments). That implementation is covered by
aggressive patent attorneys.

Oracle implements locking at row level within each data block. The block
header expands dynamically to accommodate a list of transactions that
can access, with minimum and maximum sizes settable by the DBA. This
works reasonably well.

Each SELECT FOR UPDATE is actually a block-write, whether or not the
rows are modified, which has some additional code to recover from this
without crashing/redo. Later transactions end up cleaning up the lock
header info (which later became a problem in Parallel Server).

https://cwisdb.cc.kuleuven.ac.be/ora10doc/server.101/b10743/consist.htm

--
Best Regards, Simon Riggs