1

I'm using ASP.NET MVC3 and Entity Framework with Database first aproach. I have a table with id(key), number and description columns. I need to generate a number after item insertion. I created instead of trigger where I generate number that I need (it depends on last number that allready exists in database). The problem is: When users insert items at same time the number in both cases is the same. So for both inserted items trigger fires and inside the trigger select returns same last number of item that allready exsist.

What is the best practice to solve this kind of issue? Thanks.

dkrzh
  • 45
  • 5

1 Answers1

1

I believe this will help you.

http://www.sqlteam.com/article/custom-auto-generated-sequences-with-sql-server

Also, if you're using SQL Server 2012, there is a new feature called SQL Sequence. It's something that Oracle databases have for a long time.

If you need those numbers without any gap, I'd suggest you to lock the table (prevent write), query it, update the row with max(number) and unlock it.

Milivoj Milani
  • 315
  • 2
  • 11
  • Thanks, helpful article. About your suggestion(prevent write), how can I lock a table? – dkrzh Nov 30 '12 at 05:49
  • Beware of this. You might lock the table for all transactions. Locking SQL table is done by SELECT ID,NAME FROM USERS WITH (TABLOCKX) No other processes will be able to grab any locks on the table, meaning all queries attempting to talk to the table will be blocked until the transaction commits. See more at http://stackoverflow.com/questions/5102152/tablock-vs-tablockx – Milivoj Milani Nov 30 '12 at 13:11
  • What might help you is to change the isolation level of the transaction to REPEATABLE READ. There is an article going into great detail about it in here > http://www.gavindraper.co.uk/2012/02/18/sql-server-isolation-levels-by-example/ – Milivoj Milani Nov 30 '12 at 13:16