[Dbix-class] SQLite problem (cannot update in iterator loop)

[Matt S Trout]

On Thu, Jan 19, 2006 at 01:00:20PM -0500, Christopher H. Laco wrote:
> Dan Sully wrote:
> > * Daisuke Murase shaped the electrons to say...
> > 
> >> This script shows following error:
> >>
> >>    DBD::SQLite::st execute failed: database table is locked(1) at
> >>    dbdimp.c line 398 at
> >>    /usr/local/share/perl/5.8.4/DBIx/Class/Storage/DBI.pm line 187.
> >>    Use of uninitialized value in numeric eq (==) at
> >>    /usr/local/share/perl/5.8.4/DBIx/Class/Row.pm line 116.  Can't
> >>    update DBIC::Test=HASH(0x85a8c3c): row not found at dbic_test.pl
> >>    line 20
> > 
> > This is an unfortunate limitation with DBD::SQLite. Not SQLite itself,
> > which
> > can handle multiple readers and one writer.
> > 
> > -D
> 
> 
> There is some DBIC-ness in this problem as well.
> 
> When I converted Handel to a DBIC branch, I had the same problem as well
> with iterators (foreach items->next). But, if I changed them to use an
> array instead (@items = ->items; foreach item @items)), all worked well.

Yep. That's because a DBIx::Class resultset is actually a resultset against
the database. So when you call ->next it fetches a record and inflates it.

That means that in DBD::SQLite you've got an open reader against the table,
which locks it so it can't be altered, thus giving you inconsistent data.

It doesn't show up on Class::DBI because Class::DBI fetches all data from
the sth into an arrayref, instantiates the Iterator with that, and throws
the sth away. This, as you can imagine, works really well for iterating over
thousands of results ...

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +