ADO.NET concurrency while inserting

[wym]

I have read a bit about pessimistic and optimistic concurrency but I'm not
sure if I understand it correctly.

I would like for multiusers to insert into a table, but first off the
application calls a function that querys for the max unique key of the
table. Then the application adds 1 to find the next sequential unique key.
Using this new unique key, it inserts a new record.

From what I understand of pessimistic and optimistic concurrency, they both
deal with protecting data while updating records. The problem is, I'm not
updating, I'm concurrently inserting.

Example:

User A gets a max unique key from table X which happens to be 100
User A adds a record to table X with unique key of 101 (100+1)
Meanwhile...
User B gets a max unique key from table X which also happens to be 100
User B adds a record with 101 as the unique key as well
It should be...
User B gets max unique key of 101
User B adds a record of 102

Can anyone explain how concurrent inserts are done?

Thanks in advance.

 

[Stephen Muecke]

wym

Why not just create an autoincrementing column in your table?
There is no need to waste valuable resources querying the table and no
concurrency issues with the unique key column
When you insert a new record, set your rows unique key column to the value
returned by @@IDENTITY

Stephen