Databases
StackOverflow2013
Postsdbo
POActiveRecord: Avoid inconsistency in has_many relations
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POActiveRecord: Avoid inconsistency in has_many relations
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USreto
    1. USreto
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POActiveRecord: Avoid inconsistency in has_many relations
      1. USDeepak N
      1. VTFavorite
    2. VO
      singulars
      1. POActiveRecord: Avoid inconsistency in has_many relations
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload