[Dbix-class] Paging all re nested sets

[Sebastian Willert]

On Sat, 2008-04-05 at 17:06 +0200, Moritz Onken wrote:
> Am 04.04.2008 um 16:56 schrieb Sebastian Willert:
> >
> > Guilty as charged, your honor. I still have an half-backed
> > implementation of DBIx::Class::Tree::NestedSet laying around, that  
> > I've
> > almost forgotten about. In my defense, I'd stopped working on it  
> > because
> > I believe we'd need a good RDBMS-independent locking mechanism (and an
> > live object index for bonus points) for any nested-set  
> > implementation to
> > become production-ready. In difference to adjacency lists, nested sets
> > tend to die a horrible dead when used without a good locking strategy
> > (preferably row-based).
> 
> Why can't we use transactions? If someone might want to run nested  
> sets on a dbms which does not support transactions it is on his own  
> risk. I think nested sets will only be used in big sites where  
> retrieving the hole tree or a recursive retrieval is not an option.  
> And these sites use probably dbms which support transactions.
> 
> Those who want trees but have no good dbms have to fall back to the  
> simple tree approach (parent_id ...)

Unfortunately it's not as easy as this. Off the top of my head:
transactional isolation just prevents dirty reads wrt. this problem,
i.E. reading an inconsistent state. If two concurrent operations want to
change the same tree they are not forced to wait for the other one to
complete but start operation on the same initial state unless locking is
employed. This would seriously butcher the nesting information.

Have a look at
http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html for a
short discussion (and yeah, I know mysql doesn't necessarily count as a
good RDBMS).

Cheers,
  Sebastian