1

I am working with an application that generates invoice numbers (sequentially based on few parameters) and so far it has been using a trigger with serialized transaction. Because the trigger is rather "heavy" it manages to timeout execution of the insert query.

I'm now working on a solution to that problem and so far I came to the point where I have a stored procedure that do the insert and after the insert I have a transaction with isolation level serializable (which by the way applies to that transaction only or should i set it back after the transaction has been commited?) that:

  • gets the number
  • if not found do the insert into that table and if found updates the number (increment)
  • commits the transaction

I'm wondering whether there's a better way to ensure the number is used once and gets incrementer with the table locked (only the number tables gets locked, right?).

I read about sp_getapplock, would that be somewhat a better way to achieve my goal?

pzaj
  • 1,062
  • 1
  • 17
  • 37
  • I think without seeing where the problem occurs in your code, the best you're going to get is a shot in the dark. – Tab Alleman Jul 16 '15 at 13:08
  • If there's only one source of sequential numbers, use the identity column. If there is more than one source and your stored procedure picks the correct one each time, use [sequences](https://msdn.microsoft.com/en-us/library/ff878091%28v=sql.120%29.aspx). – GSerg Jul 16 '15 at 13:12
  • @GSerg that seems interesting, I'm not a DB guy and I didn't know about sequences at all. There's one more thing I need from that solution - I must be able to easily modify the number of the next sequential number. Will I be able to do that with sequences? – pzaj Jul 16 '15 at 13:19
  • @GSerg I cannot use sequences, I must leave it SQL Server 2008 compatible. – pzaj Jul 16 '15 at 13:39
  • @user1970395 It is very unclear what your setup even is. How do you calculate numbers, where and why do you store them? – GSerg Jul 16 '15 at 13:49
  • @GSerg Alright, I'm using a table "numbers" that stores year, month, office, type and obviously next number - based on those 4 parameters I need to get this number, then I increment it (only for a row where those 4 parameters matched), then I create a real alphanumeric invoice number based on this sequence number. – pzaj Jul 16 '15 at 13:55

2 Answers2

1

I would optimize the routine for update (and handle "insert if not there" separately), at which point it would be:

declare @number int;

update tbl
set @number = number, number += 1
where year = @year and month = @month and office = @office and type = @type;

You don't need any specific locking hints or isolation levels, SQL Server will ensure no two transactions read the same value before incrementing.

If you'd like to avoid handling the insert separately, you can:

merge into tbl
using (values (@year, @month, @office, @type)) as v(y,m,o,t)
on tbl.year = v.year and tbl.month = v.month and tbl.office = v.office and tbl.type = v.type
when not matched by target then
  insert (year, month, office, type, number) values(@year, @month, @office, @type, 1)
when matched then
  update set @number = tbl.number, tbl.number += 1
;

Logically this should provide the same guard against race condition as update, but for some reason I don't remember where is the proof.

Community
  • 1
  • 1
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Thank You @GSerg, I beleive that should solve the issue, despite the one I described in my question works as expected with no problems and deadlocks (or timeouts), your first solution seems way clearer! Are You sure it does not require transaction? ;> Because it's being used inside SP which is a batch, not a transaction (unless .NET wraps it into transaction in background which I'm not sure) – pzaj Jul 17 '15 at 06:46
  • This particular query does not need a transaction for the purpose of avoiding two clients generating same id. For anything else you certainly should use a transaction whenever you have more than one statement that are supposed to work together. – GSerg Jul 17 '15 at 08:18
0

If you first insert and then update you have a time window where an invalid number is set and can be observed. Further, if the 2nd transaction fails which can always happen you have inconsistent data.

Try this:

  1. Take a fresh number in tran 1.
  2. Insert in tran 2 with the number that was taken already

That way you might burn a number but there will never be inconsistent data.

usr
  • 168,620
  • 35
  • 240
  • 369
  • If you insert and update in the same transaction, only those who are happy with dirty reads might see it, otherwise there is no time window for the invalid number. – GSerg Jul 16 '15 at 13:14
  • @GSerg I agree, also the drawback of proposed solution is that I must ensure insert will happen otherwise I may have a gap and by the financial law of any country I believe there must be no gaps in invoice numbers. – pzaj Jul 16 '15 at 13:21
  • @usr and I also don't mind that invalid number may be observerd, the insert and the rest executes in about 30 ms so the time window is rather no-time. – pzaj Jul 16 '15 at 13:36
  • @user1970395 did I understand you right that you are using two transactions right now? Then you have potential data inconsistency. Can't do that if inconsistency is not allowed. – usr Jul 16 '15 at 13:48