[LRUG] Scoping sequences to a parent

[Eaden McKee]

Locking can be tricky,

MySQL's auto increment should be avoided for your purpose because it
happens outside a transaction ( if you rollback a transaction the table
auto increment doesn't go back to what it was)

I'm not sure what deadlocking issues you're having, but this could work,
however non-concurrently per parent:

parent.with_lock do
Child.create!(idx: parent.child_idx + 1)
parent.child_idx += 1
parent.save!
end

If you're getting deadlocks because too much stuff is happening inside the
lock, you could seperate the create into a create and then update with more
processed information, as the update doesn't need the lock on the parent.

Eaden

@eadz


On Fri, 2 Sep 2016 at 11:34 AM, Andrew Stewart <boss at airbladesoftware.com>
wrote:

> > If it’s just the index of the child within the ordered collection of
> children then I’d expect that to be fine.
>
> Yes, that's what it is.
>
> > The locking behaviour depends on what autoincrement lock mode you’re
> using, but hopefully that is already working.
>
> My current (lock-y) implementation doesn't use MySQL's autoincrement
> feature.  It's simply an integer column in the parent which is incremented
> in the child's after_create callback, which will be part of the same
> transaction.
>
> class Parent < ActiveRecord::Base
>   has_many :children
>
>   def next_child_number
>     increment! :child_number
>     child_number
>   end
> end
>
> class Child < ActiveRecord::Base
>   belongs_to :parent
>
>   after_create :set_number
>
>   def set_number
>     n = parent.next_child_number
>     update_column :friendly_id, "#{other_stuff}-#{n}"
>     update_column :number, n
>   end
> end
>
> > Your subsequent email suggests that you want to actually store this
> value against the child rather than derive it, which isn’t quite what I
> meant, but it should be doable. Once the autoincremented ID has been
> assigned, the child index is fixed (i.e. you can safely assign it with an
> UPDATE whenever is convenient) as long as you don’t allow previous children
> to be deleted.
>
> Which begs the original question: how to derive the child index safely?
>
> (As far as I can remember from 8 years ago, I implemented it the way I did
> because it seemed more efficient to just keep an index counter on the
> parent than to query the child table each time.  Although I don't
> understand yet why I'm getting occasional deadlocks / uniqueness
> violations, I assume it's because of the involvement of the parent table.)
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> Archives: http://lists.lrug.org/pipermail/chat-lrug.org
> Manage your subscription: http://lists.lrug.org/options.cgi/chat-lrug.org
> List info: http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20160902/e4ec669d/attachment-0002.html>