Correct Isolation level for SQL command

Question

I want to run this SQL Statement to insert a new rank to the Rank table. The primary key rank_id is a char field so its not auto incremented. So I use TOP keyword to get the last ID and then increment it in SQL.

const string InsertStatement = @"BEGIN DECLARE @ID INT = (SELECT TOP 1 (rank_id + 1) FROM Rank ORDER BY [rank].rank_id DESC) 
                               INSERT INTO RANK (rank_id, rank_name, shift_rate, revised_date) VALUES (@ID, @rank, @rate, @date) END"

I think that I should handle this transaction to avoid dirty reads other wise another user might read a wrong ID as the last one. Am I correct? Then what is the correct isolation level for a this sort of situation? What about this...

using(var Trans = sqlConnection.BeginTransaction(IsolationLevel.Serializable))

@Rahul I think that's what he's asking... does he really need to use something as brutal as Serializable, or can he get away with something less restrictive (like ReadCommitted). — Lynn Crumbling
@Rahul, In ReadCommited, it says... Data can be changed by other transactions between individual statements within the current transaction... What does it mean by can be changed? — CAD
If you're willing to upgrade to SQL 2012 Express, you could use a Sequence. That should allow you to avoid the issue entirely. — Dave Mason
"Roll your own" incrementing numbers like this are fraught with issues. Concurrency is a MAJOR issue with this type of thing. If you are going to stick with this I would recommend using MAX(rank_id) + 1 instead of TOP 1 with an order by. — Sean Lange

Philip Kelley Philip Kelley · Accepted Answer · 2014-08-15T13:55:22

You could tackle this from the SQL end. The following can do the trick:

INSERT RANK (rank_id, rank_name, shift_rate, revised_date)
 select max(rank_id) + 1, @rank, @rate, @date
  from RANK

It's a single statement and thus an Atomic transaction--that is, it gets it's own lock(s) on the table (and index if there is one), ensuring that no other simultaneous run of the exact same query can "grab" the next value.

Dirty reads are all but impossible, since it's all done in a single (and probably extremely fast) statement. This gets you Atomocity -- individual statements are always bound within their own "implicit transaction". Slap a primary key constraint on the column, and you should be done.

I feel obligated to mention that you will have problems if there are non-numeric values in your character column...

Correct Isolation level for SQL command

1 Answers