Puzzled By Concurrent Update

I have a puzzle in my mind here. I will thank anyone who can solve my puzzle.

I am not familiar with SQL and its theories behind, so please bear with me if I am asking a stupid newbie question.

My puzzle is generally a problem of generating sequence numbers. The following SQL is only a stripped down version - it fetches the max number, add 1 to it and updates the table with the new number.

DECLARE @max int

SELECT @max = MAX(next_number) + 1 from sequence_numbers

UPDATE sequence_numbers SET next_number = @max WHERE next_number = @max

Now if user1 gets 100 and user2 also gets 100 and they both try to update the table, what would happen? I fear that the result would be 101.

One of my coworker thinks that adding 'WHERE next_number = @max' can solve the conflict - user2 will fail. His reasoning is like this:

After user1 updates the table, next_number would be 101 and user2's update will fail because his WHERE criteria is still 100.

But I think user2 still sees the old data (100) and still succeeds and thus both users update the table with number 101.

Thanks.