Hi,

I have a new entity i want to save to the database. This entity has an ID as a PK. This ID is not a autoidentifier, but I do want it to be the next in line. We used to use a SP which would get the MAX and add 1 to this. Is there a way I can do this in one Save action, or should I use a transaction and first get this value and then save the entity?

I am using Self Servicing...

Gr.,

G.I.

Use an identity column. Procs which do a MAX on a table arent safe per se (only with a serialized transaction, which is slow) and also leave gaps: you HAVE TO discard a value retrieved if the transaction failed, you can't re-use a value in that case. So the 'next in line' will never be 100% the next in line, it might be thread A (a website is multi-threaded) retrieves 10, thread B retrieves then thus 11, the transaction of A fails, and you'll have 8, 9, 11 in your db, not 10.

Otis wrote:

Use an identity column. Procs which do a MAX on a table arent safe per se (only with a serialized transaction, which is slow) and also leave gaps: you HAVE TO discard a value retrieved if the transaction failed, you can't re-use a value in that case. So the 'next in line' will never be 100% the next in line, it might be thread A (a website is multi-threaded) retrieves 10, thread B retrieves then thus 11, the transaction of A fails, and you'll have 8, 9, 11 in your db, not 10.

Frans, the scenario you describe will also leave gaps (at least when you are using SqlServer). If the transaction fails, the identity value will be used up.

Correct, avoiding gaps is not possible, unless you have a single access point to the table, i.e.: single threaded routine, using a serialized transaction, and every save action is sequential.

Not what you want

But thanks for correcting me, I forgot to mention that

Yeah, I know I should do that, the problem though is that with using integration with other products we can't just just autoidentity. We give the company of the application the possibility to USE autoidentity (in this case using this MAX feature) or use the ID from an another application.

This means that I can't use the Autoidentity in my case, which brings me back to the question in the first posting

Gr.,

G.I.

In that case, use a transaction (isolation level serialized) to call your proc to retrieve the value. Commit the transaction and store the value in the entity property and save the entity in another transaction, commit that one as well.

Ok...that answers the question that there is no easier way to do this It's working correctly like this, thanks!