Here is a thread were I discussed creating unique ids. Can’t you create these ids at creation of your entities, then use them in your assignment query?
Your solution will not work, as you can have a race condition where any number of threads match on Foo and calculate the same max value before each creates a new Foo with the next number. This is because you have not done anything to serialize the calculation of the next sequence number.
What I have done is create a node for each sequence generator I need. I then query for this node, update its sequence number, and return the new value. I do this in a transaction and I lock the node with a 'se…