Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 28 '11 at 12:25

This question came from our site for professional and enthusiast programmers.

    
Related question from same user: stackoverflow.com/questions/6708008/… –  JNK Jul 15 '11 at 16:08

4 Answers 4

up vote 13 down vote accepted

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...

share|improve this answer
    
+1 - Please see his other post where I tried to explain this to him :) –  JNK Jul 15 '11 at 16:06
    
+1 absolutely - let the database handle the management of ID's .... –  marc_s Jul 15 '11 at 16:07
3  
+1: This is precisely why "rolling your own counter column" is not recommended. Thankfully, Denali brings sequence support now that they are ANSI –  OMG Ponies Jul 15 '11 at 16:07
    
@marc_s, @OMG Ponier, I am clear and I agree with your opinion. The thing I asked was timing of execution of two same quries. I understand both are different. The thing I dont know is wil SQL Server execute both at the same time (as SQL Server support multiple connections) or one by one ? –  user576510 Jul 15 '11 at 16:20
    
And I would add that if you are inserting to multiple tables and do not have good transaction processing, it is possible to associate the child records from prcess b with the parent record for process A even though the insert for process B in the parent table failed. –  HLGEM Jul 15 '11 at 20:02

If there are no constraints in place to prevent duplicates, it will allow both.

Use an Identity field as I recommended in your other question on a similar topic.

If there are constraints, then one of them will be a millisecond before the other. It's not realistic to think they would be PRECISELY the same time. The first one will perform the insert, and the second will wait while the first has a lock in place. Once the first releases the lock, the second will attempt it's insert and fail on a duplicate key insert.

share|improve this answer
    
JNK if there are no constaints will both insert in one time be executed together or one by one ? As SQL Server support multiple connectiosn in one time. How more then one requests are treated in one time ? Will be be executed together ? –  user576510 Jul 15 '11 at 16:11
1  
@user576510: both INSERTs will be separate. Always. They are different connection/processes. Nothing combines these at all. If you have no constraint, your data will get corrupted... –  gbn Jul 15 '11 at 16:14
    
@gbn, @JNK, I understand both will be seprate but my question is about their handling timing. Will both be executed together or one by one ? –  user576510 Jul 15 '11 at 16:17
    
@user576510: Neither. Discrete but overlapping –  gbn Jul 15 '11 at 16:23
    
I disagree with the following: "If there are constraints, then one of them will be a millisecond before the other. It's not realistic to think they would be PRECISELY the same time". Collisions in RDBMS do happen, we do observe them, provided that we have enough activity. –  A-K Jul 15 '11 at 18:00

Your question is very common. It would take at least fifty pages to just give you some introduction. If you want to try out and see for yourself, I have written a few examples when two connections simultaneously access one and the same row: Developing Modifications that Survive Concurrency

share|improve this answer

Using the internal Identity type for SQL server is the correct solution but the question was to "learn how SQL Server internally handles more than one request at the same time." Since I don't see that in any answers here I will explain. The Identity type both iterates AND stores a new value atomically. The old value is given as the identity for one requester only. An identity is not issued until the next value is persisted (see http://stackoverflow.com/questions/2009063/are-disk-sector-writes-atomic). All requests for a new value of the identity column will happen serially and no duplicate key will occur unless you wrap your 64bit identity or duplicate a UUID which is virtually impossible. One thousand concurrent requests should give that many unique identities.

Atomic operations are a key feature of database servers if not their most important aspect.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.