[lug] MySQL work queue problem

[Michael J. Hammel]

On Fri, 2006-10-06 at 14:35 -0600, Jason Vallery wrote:
> Thanks, that is an interesting way to approach the problem.  I'd
> initially hoped that I could have the select statement do the update,
> but this should work equally well.

FWIW, we use Java on a project here that makes use of
javax.transaction.TransactionManager interface.  I'm far from
knowledgable about this (I'm a C hacker by trade), but it appears you
can create a whole slew of code inside the transaction that, apparently,
is supposed to be run atomically, including db updates/queries.  Of
course, I've overused that on more than one ocassion and ended up trying
to do a db update and then db lookup inside the same transaction, which
isn't guaranteed to work the way I expected it.  On Linux it seems to
work fine. On Windows, we sometimes have the lookup happen before the
update completes.  At least I think that's what's happening.

> I did notice a weird syntax on the MySQL website that I don't quite
> understand: 
> 
> select ... for update;

I've noticed some of these statements in our code too (I didn't write
them).  They appear to provide a mechanism for row locking of the
selected rows.  What happens is you make a call to this method (re:
function) that does the "select ... for update", which locks a set of
rows for doing updates.  As far as I can tell they get unlocked (perhaps
on a row by row basis, as opposed to all at once) as soon as the update
completes.  There doesn't seem to be a corresponding "unlock" method.  

This lock method is called inside one of the transaction-managed methods
so that both the code and the db update are, essentially, atomic.  This
is, of course, at the expense of serializing what was otherwise threaded
code.

BTW, we use InnoDB tables, so I don't know if any of this helps your
situation.
-- 
Michael J. Hammel                                    Senior Software Engineer
mjhammel at graphics-msue.org                           http://graphics-muse.org
------------------------------------------------------------------------------
The main accomplishment of almost all organized protests is to annoy people 
who are not in them. -- Unknown.