Recently I needed to insert a row into a Microsoft SQL database that had a column value that had to be based on an increasing sequence number from other rows in the same table.
For example, each row in the table already might have a column called "SequenceNum" that represented an increasing number for each row. The sequence number would need to be specific to some other key in the table, for example, a "Message" column.
A MAX statement would need to be used to determine the next sequence number, but to ensure that two processes/threads don't each execute the SQL statement at the same time (and end up with the same SequenceNum), a lock would need to be used to isolate each process/thread. One possible SQL statement might be:
-- Assume you have a parameter for Message called @MessageKey
INSERT INTO TableA (SequenceNum, Message)
SELECT
ISNULL((SELECT MAX(SequenceNum) + 1 FROM TableA WITH (TABLOCKX) WHERE Message = @MessageKey), 1)
, @MessageKey
The ISNULL statement is used in case this is the first row inserted for a specific message.
