Suppose a SQL Server table has two MailID columns, with max value = 5.
If two different users at the same time execute insert queries that try to insert Max(MailId)+1 into the MailID column, is it possible that both new rows may get the value 6?
I want to learn how SQL Server internally handles more than one request at the same time.
Best Answer
Yes, it's possible (as per JNK's answer: 2 overlapping MAX reads, both get 6, one fails with duplicate assuming a constraint)
Use IDENTITY columns for this very reason.
If you have some odd notion to not use IDENTITY, then you have to use lock granularity (TABLOCKX ) or semaphores (sp_getapplock) to restrict concurrency and allow only one process to run MAX at a time.
Edit:
Two requests that come in at the same time (or very close to it) run concurrently. They are both separate from each other but overlapping.
So each process will read the table using MAX and both get 6. At this point, both processes and reading and the INSERTs have not started yet.
The leading processes INSERTs 6, closely followed by the lagging process. The lagging process will get the unique key violation (you have uniqueness, right?)
If the gap between processes is enough so that the 2nd read happens after the 1st INSERT you're OK.
However, if you have enough calls that you have this risk of duplicate, then using locks/semaphores to decrease concurrency is madness...