[LRUG] Scoping sequences to a parent

[javier ramirez]

Hi,

For this type of thing I really love Redis. You can create a counter per 
parent and atomically increment without worrying about concurrency. It's 
also blazingly fast, so you don't have to worry about bulk imports and 
performance problems, and unlike memcached you can add persistence so 
you'll never lose your counters.

Chances are you are already using in your system, maybe for caching, 
maybe as a dependency for sidekiq, or as the actioncable backend, so you 
might not even need to install a new component. And if you have to 
install it, Redis is very clean to install, easy to monitor, and very 
robust.

You would still use the callback as you are doing now, but you would 
increment without having to read first. A side effect you might get is 
having gaps in the sequence if a model increases the counter and then 
rolls back the transaction. If that's not a problem for you, then I'd 
strongly recommend to go with Redis.

Cheers,

j



On 02/09/16 11:11, Andrew Stewart 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


-- 

javier ramírez

javier ramirez's home page (http://javier-ramirez.com) 
<http://javier-ramirez.com>
javier ramirez's blog (http://formatinternet.com) 
<http://formatinternet.com>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20160902/05d073f3/attachment-0002.html>