Hi all,
I have a problem where I need to keep and increment an object number field in a table, however this number should be unique within given logical domain, not across table globally.
An example would be multiple Businesses scheduling multiple Jobs; Job.Number should be unique within a business.
So I basically need to make sure that concurrent job creation operations do not produce Jobs with the same Number.
Currently I see only one way to implement that (in postresql):
Lock the table with a self-locking type of lock, say "SHARE UPDATE EXCLUSIVE" so all the other operations of this type have to queue and wait, thus ensuring that MAX() function always returns unique value.
However there seems to be a huge drawback in that solution - it essentially creates a bottleneck for all INSERT operations into Jobs table.
I don't think I can use Postgreql sequences, because:
- I do not want to create a new sequence for each new business
- It can have gaps
would you suggest any other ways to deal with that problem?
Thanks!