On Oct 23, 2010, at 11:04 AM, Adam Medveczky wrote: > Hi! > > I have a table with a compound key - the second is always known, and > if the first one is not supplied explicitly, I would like to make it > equal max(first_key) + 1. > > I would like to do this with concurrency in mind, there could be two > such requests at once, and I don't want them to get the same number - > the 2 inserts may not conflict at all, as they could differ in the > second part of the key. > > What could be the best solution for this? (If it's possible I'd like > to avoid having a second table with a counter + lock).
In order of preference: 1. use a sequence 2. for an optimistic approach, place a UNIQUE constraint, or some appropriate CHECK constraint if the rules are more complex, on the "id" column 3. embed a SQL expression into the INSERT: myobject.id = select([func.max(mytable.c.id) + 1], for_update=True).as_scalar() You should consult your DB's documentation for the most appropriate FOR UPDATE hint. > > Thanks in advance, > > Adam > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
