CODEWITHSUNDEEP
sqlsql-servert-sqltransactions
pzaj
pzaj

Reputation: 1092

SQL number generation in concurrent environment (Transation isolation level)

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:

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?

Upvotes: 1

Views: 300

Answers (2)

GSerg
GSerg

Reputation: 78185

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.

Upvotes: 1

usr
usr

Reputation: 171188

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.

Upvotes: 0

Related Questions