On Sat, Jan 06, 2007 at 12:04:26AM -0500, Perrin Harkins wrote:
> John Siracusa wrote:
> > Finally, if you only want to turn this on for particular calls, remember
> > that the db is an optional argument to all Manager methods. You can always
> > make/get a $db, yank out the $dbh, set the attribute manually, and pass the
> > $db as a parameter to your Manager calls.
> >
> > $db = My::DB->new(...);
> > $db->dbh->{'mysql_use_result'} = 1;
> > $iterator = MalwareLog::Manager->get_blah_iterator(db => db, ...);
>
> I recommend doing it this way, and turning it off after your large
> query, unless this is essentially a single user reporting database.
> When you turn on mysql_use_result and do a query on a MyISAM table, it
> will keep a read lock on the table until you finish with that statement
> handle.
The lock is held on whatever table the final rows are fetched from.
So it's not a problem for group-by queries, for example, because the
final rows are fetched from a temp table.
For simple selects that don't use a temp table (or where you're not sure)
you can avoid the problem by using:
select SQL_BUFFER_RESULT ...
which simply tells mysql to copy the results into a temp table first.
Tim.
p.s. Small claim to fame: I asked mysql to add that feature.
> This is not an issue with InnoDB tables because they use
> multi-version concurrency control (MVCC), so readers never block writers.
>
> If all you use it for is reports, and you don't need to handle
> concurrent updates, then it doesn't matter and you can just leave it on
> all the time.
>
> - Perrin
>
> -------------------------------------------------------------------------
> Take Surveys. Earn Cash. Influence the Future of IT
> Join SourceForge.net's Techsay panel and you'll get the chance to share your
> opinions on IT & business topics through brief surveys - and earn cash
> http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
> _______________________________________________
> Rose-db-object mailing list
> Rose-db-object@lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/rose-db-object
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys - and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Rose-db-object mailing list
Rose-db-object@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/rose-db-object
