Re: [Ruby-DBI-devel] Mysql transactions, and postgresql anomoly

[Brian C. <B.C...@po...>]

On Thu, Mar 27, 2003 at 04:21:26PM -0600, Paul DuBois wrote:
> Should I add the @mutex thingies?  The reason I didn't, when
> adding the transaction support, is that I am sufficiently Ruby-illiterate
> not to know whether or not they're needed.  And not for any more technical
> reason than that! :-)

Well, I've picked up a fair amount of Ruby, but there's plenty that I don't
understand about Mysql :-)

One of the things that seems apparent about the Mysql API is that you have
to issue multiple API calls to complete one 'action'. For example, 'do' does
the following steps:

      @handle.query_with_result = false             # << API
      @handle.query(sql)                            # << API
      @handle.affected_rows     # return value      # << API

and 'execute' does these:

      @handle.query_with_result = true               # << API
      @res_handle = @handle.query(sql)               # << API
      @column_info = self.column_info
      @coerce = DBI::SQL::BasicQuote::Coerce.new
      @current_row = 0
      @rows = @handle.affected_rows                  # << API

Each @handle method call is a call into the C API.

Now, Ruby's threading system is not as fine-grained as OS threading, where a
context switch between two threads could happen almost anywhere. In
particular, once it has committed to calling a C function, e.g.

      @handle.query(sql)

then I believe that library function will run to completion; there's no way
that Ruby's threading can interrupt it.

However, in normal operation, a context switch _could_ happen in between any
two Ruby expressions. Imagine if you have thread 1 trying to do an
'execute', and almost at the same time thread 2 is doing a 'do'.

Thread 1 starts by:

      @handle.query_with_result = true

Now let's say that the Ruby threading mechanism decides that now would be a
good time for a context switch. It then moves onto thread 2, which does

      @handle.query_with_result = false
      @handle.query(sql)
      ...etc

At some time later, thread 1 gets a look in again. It then does the next
stage of its operation,

      @res_handle = @handle.query(sql)

However, this will be broken, because the @handle is in the wrong mode
(query_with_result has been set to false in the mean time)

Therefore, because one Mysql 'operation' is in fact multiple API calls, you
must ensure that while you are doing a sequence of operations on @handle,
nobody else does anything on @handle either.

That's where the mutex comes in. Once a piece of code has grabbed the mutex,
it doesn't prevent a thread context switch happening; but what it does mean
is that if any other thread also tries to grab the mutex, it will be stopped
until the mutex becomes free again.

So if thread 1 does:

    @mutex.synchronize {
      @handle.query_with_result = true
      @res_handle = @handle.query(sql)
      @column_info = self.column_info
      @coerce = DBI::SQL::BasicQuote::Coerce.new
      @current_row = 0
      @rows = @handle.affected_rows
    }

and in the middle of that a context switch occurs, and thread 2 tries to do

    @mutex.synchronize { 
      @handle.query_with_result = false
      @handle.query(sql)
      @handle.affected_rows     # return value
    }

it will block at the @mutex.synchronize. Thread 1 will get its turn back
sooner or later, complete its work, at which point thread 2 can grab the
mutex and have its go.

Back to commit, rollback and set autocommit. None of these operations return
a value, which keeps things simple. However, I would imagine that it is a
good idea to tell the API 'query_with_result = false' before issuing them.
In that case, you'd want to do

    @mutex.synchronize {
      @handle.query_with_result = false
      @handle.query("commit")    # or whatever
    }

Otherwise, when you issue "commit" then the state of the query_with_result
flag is indeterminate, and I don't know enough about mysql to say if that's
safe or not.

But even if it is, and the single operation @handle.query("commit") is all
you need, you still don't want to issue it whilst another thread is in the
middle of performing some other query. You could destroy the results of that
query, for example corrupting the value in @handle.affected_rows before it
has been read.

The only way prevent this is cooperation between the methods: *all* methods
which have any side effects on @handle must agree to grab the mutex while
performing them. So I'd say that the commit/rollback/autocommit methods
really ought to honour it as well.

Regards,

Brian.