Auto-increment the primary key without setting the identity

I am using the following syntax to insert new records (I don't want to have duplicate keys):

insert into tbl(key) values select max(key)+1 from tbl

开发者_开发知识库

Someone says it will have concurrency problem.

Is that right?

SELECT -> LOCK table -> INSERT

or

LOCK table -> SELECT -> INSERT

Which one of the above is correct?

---

If this happens inside a transaction you will be fine.

---

If you're doing replication, your best bet is to use GUID's as a primary key and also "please" do not create clustered index on that column.

---

The select will lock the table, and you are doing everything in a single statement, looks like you should be fine (other than maybe performance to calculate the max(key)).