[LRUG] Scoping sequences to a parent

[Stephen Best]

I'm not surprised you're having deadlocks, I assume you're using those
columns as a composite primary key which would mean the whole table is
going lock while you figure out the next id in the sequence.

I wonder if it would be practical for you to just emulate this behaviour by
having a regular auto increment sequential ID for the whole table and a
regular foreign key to the parent then to find 69/48 you could do something
like:

`SELECT * FROM things WHERE parent_id = 69 ORDER BY id OFFSET 47 LIMIT 1`

I'm sure this has plenty of issues but it would remove a bottle neck and
solve a potential tricky consistency problem.

I hope there's not an off by one error in there :D


Bestie.

@thebestie

On 2 September 2016 at 10:11, Andrew Stewart <boss at airbladesoftware.com>
wrote:

> Hello LRUG!
>
> I have a Rails app using MySQL.  In the app I have a parent model with
> children and I would like to number the children scoped to the parent –
> much as GitHub issues are numbered by repo.
>
> MySQL autoincrement sequences would be ideal if they could be scoped by
> parent but I don't think that's possible.
>
> For the past 8 years each parent model has held a counter.  In an
> after_create callback in the child model, it calls a method on the parent
> which increments the counter and returns it; the child updates itself to
> store that value.
>
> This generally works but I've noticed I get the odd deadlock and
> uniqueness violation (there's a uniqueness constraint on the child for
> [parent_id, number]).  These errors crop up when people are importing large
> numbers of children into the parent's collection.
>
> An alternative might be to change the after_create callback to query the
> table for the maximum number among the parent's children, add one, then
> update itself with that value.  Then the parent table wouldn't be
> involved.  But it still feels a bit hacky.
>
> This strikes me as the sort of thing for which there must be a standard
> solution.  Any hints would be appreciated!
>
> Many thanks in advance,
>
> Andy Stewart
> _______________________________________________
> 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/8e41a22d/attachment.html>