1

I want create a Stored Procedure that insert data to 3 table using transactions. I get the last primary key value of the Main table using MAX. It takes a little time to get it.

My problem is here where some request come in same time and all of them get same result for last record. How can I lock transaction or other solution to it?

I know I can make an identity field and use it with SCOPE_IDENTITY, but don't want do this, unless I'm forced to do it.

ughai
  • 9,830
  • 3
  • 29
  • 47
Siamak Ferdos
  • 3,181
  • 5
  • 29
  • 56
  • Check out this link http://stackoverflow.com/questions/3662766/sql-server-how-to-lock-a-table-until-a-stored-procedure-finishes – SubqueryCrunch Jun 24 '15 at 11:10
  • 1
    Why wouldn't you want to use `SCOPE_IDENTITY`? It appears you know what the most accurate, and efficient solution is but don't want to use it? – GarethD Jun 24 '15 at 11:12
  • I make primary key with some rule that I used it as e reference at other table. So if I do it now I have to change most of my database and codes. – Siamak Ferdos Jun 24 '15 at 11:15
  • 1
    "It takes a little time to get it", it will take more time if you introduce locks. The answer to this question is: Use IDENTITY. Everything else is going to be slow, error-prone, and/or hackish. – Lasse V. Karlsen Jun 24 '15 at 11:31

2 Answers2

1

The Best way would be to use Identity or Sequence for your primary key. You can add another column for your user generated unique key which is based on the logic for other tables.

Identity Approach

In essence what you would do is:

  1. Insert row in main table.
  2. Calculate your unique key using MAX(unique key) where id < SCOPE_IDENTITY() and your additional logic and update the main table
  3. Insert in other tables

Locking Approach

If you want to lock transactions (not recommended), you can use serializable transaction with with(UPDLOCK)do something like .

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
BEGIN TRAN  
    SELECT MAX(ID) FROM MainTable with(UPDLOCK)
    // Do Stuff and generate new ID and insert into main table
COMMIT

Note: Until you COMMIT or ROLLBACK, this transaction will block both readers and writers.

ughai
  • 9,830
  • 3
  • 29
  • 47
  • That identity approach will not work without a transaction. You could have more than one insert before the first assigned a unique key and they would both read the same MAX(unique key) – paparazzo Jun 24 '15 at 12:51
0

The best way to resolve is using SCOPE_IDENTITY but you mentioned you don't want . I think you need to get the max id from a table and combine with other thing to generate a unique statement to use in your table. The best solution to do this is using a transaction with serialized level like below:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
BEGIN TRAN  
    Get your Last ID and use it . The other session will wait until you commit or rollback

COMMIT TRANSACTION-- ROLLBACK
naser daneshi
  • 284
  • 1
  • 10
  • Good suggestion, But what does happen when second request arrive? Does it rollback? If yes so how should It rise again? and if no how it continue? – Siamak Ferdos Jun 24 '15 at 11:43
  • Only setting `SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ` will not help each request get unique existing `max(id)` – ughai Jun 24 '15 at 11:48
  • @SiamakFerdos At stated clearly in the answer. "The other session will wait until you commit or rollback? You wanting to max is lot of overhead. – paparazzo Jun 24 '15 at 11:51
  • @Balm No it doesn't need to wait to all query run. Just wait until first insert that is into main table. then other sessions can go on. – Siamak Ferdos Jun 24 '15 at 11:54
  • @SiamakFerdos But the row is not actually inserted until the commit. If it does not wait and two transaction read the last committed value then both would try insert the same value. Yes it needs to wait. And you have two other tables that could cause a transaction to rollback. How many ways do you need to be told max is a lot of overhead? – paparazzo Jun 24 '15 at 12:29