Re: [Ruby-DBI-devel] Locking problem

[Matthew B. <ma...@by...>]

On Friday 05 July 2002 14:37, Michael Neumann wrote:
> Matthew Bloch wrote:
> > Hello;
> >
> > I've just noticed an undesirable interaction between multithreaded Ruby
> > applications and the MySQL C API (if not other DB APIs); my web spider
> > application was running along fine but suddenly after an unpredictable
> > number of minutes, hours etc. it would stop dead.  I think the problem is
> > that I use a "lock tables" statement which, if used with Ruby threads,
> > blocks the whole Ruby process until the table is unlocked.  Trouble is if
> > the 'thread' holding the lock is an internal Ruby thread in the same
> > process it'll never get run to unlock it :-/
> >
> > Is there a general way around this problem with the mysql_ API?  My
> > impression is no, since I can't find any way of telling mysql to use
> > non-blocking semantics through its C API.  Anyhow even if we leave it
> > like this it's probably worth adding a note about this pitfall to the
> > docs.
>
> If only your Ruby application accesses the Mysql database and you want
> to lock a table only for one thread, use a Mutex instead of locking the
> whole table. But if you lock a table because other processes (outside the
> Ruby world) should not modify the table while one Ruby thread modifies it,
> I see no other chance than using processes (start new Ruby interpreter)
> instead of threads.

I'm using multiple Ruby processes over multiple machines talking to one 
database, so the database really should be the co-ordination point for 
synchronization.  In the end this solution seemed to work quite nicely:

    private
    def lock_central
      begin
        # A bit of a bodge, but a necessary one since calling blocking Mysql
	# statements blocks *all* of our threads.
	#
	timeout(60) {
          backoff = 0.25
	  begin
            row = @dbi_lock.select_one("select get_lock('central', 1)")
	    sleep backoff
	    backoff = backoff < 5 ? backoff + rand : 0.25
	  end while row[0].to_i == 0
	}
	yield
      ensure
        @dbi_lock.select_one("select release_lock('central')")
      end
    end

Where @dbi_lock is a database connection separate from the one that accesses 
the data.  So for an overhead of an extra database connection per process and 
a slightly mucky spin lock, I think this is the best way of fixing the 
problem.

-- 
Matthew Bloch                 Bytemark Computer Consulting
                                http://www.bytemark.co.uk/
                                  tel. +44 (0) 8707 455026