title: ActiveRecord: Avoid inconsistency in has_many relations
body: <p>Assume we have an usual M-M relationship between two tables, like:</p>
  
  <p><strong>users</strong>---&lt; <strong>users_tags</strong> >--- <strong>tags</strong>. </p>
  
  <p>In this post I'm only concerned about the relation user_tags, tags: I'd like to avoid that linked tags can be deleted. Only tags which aren't referenced should be destroyable. </p>
  
  <p>The stupid way to do this would be:</p>
  
  <pre><code>class Tag
    def before_destroy  
      unless self.user_tags.empty?
        raise "error"
      end
    end
  end
  </code></pre>
  
  <p>But I think there's a potential race condition, between the check user_tags.empty? and the actual delete.</p>
  
  <p>The second approach could be to lock the whole <strong>user_tags</strong> table before checking if there are any references left. </p>
  
  <p>And the third way I can think of would involve changes to the code which creates the actual reference:</p>
  
  <p><strong>Add a reference into users_tags:</strong></p>
  
  <ol>
  <li>Fetch the Tag</li>
  <li>Lock it (to avoid concurrent destruction) </li>
  <li>Create reference in users_tag</li>
  <li>Commit</li>
  </ol>
  
  <p><strong>The before_destroy handler then could:</strong></p>
  
  <ol>
  <li>self.lock!</li>
  <li>Check if there are any references</li>
  <li>destroy self</li>
  <li>Commit</li>
  </ol>
  
  <p>Are there any better ways to do this? Which one is reliable / the best? I personally tend to the second one as it only needs logic in the before_destroy controller, but with the cost of having the whole table locked. </p>
  
  <p><strong>Edit 1:</strong></p>
  
  <p>While experimenting around with <em>LOCK TABLE</em> I realized that they are playing against my transactions. When using innodb you can either use transactions (and their locking features) or use LOCK/UNLOCK table, a mix of both worlds makes matters much much worse (LOCK/UNLOCK causes implicit commits, I missed that warning in the doc). But this just for the protocol. </p>
  
  <p>(<strong>Edit 2 (a few weeks later):</strong> I fought with that issue again. So I want to stress again <strong>Do not use LOCK TABLE</strong>)</p>
  
  <p>I'm right now tending to use a <strong>SHARE LOCK</strong> on the parent object (tag in the example)) when adding children, and a <strong>FOR UPDATE</strong> lock for deletions. But I'm still wondering if thats the way it's meant to be (Lock a Rang in the child table for a update in the parent table). </p>
  
  <p>Btw. I also realise that this question is now completely independent of rails :).</p>