Insert with function result - prevent duplicates

Question

I have a MS Sql function which has some logic to process the next ID of an element (concats letters with numbers based on some business logic, also based on a select count()).

Somehow, during the last days (in production environment), since the system is being used by more concurrent users we got some doubles.

The existing logic is very simple:

insert into XXXX (A,B,C,D,E) 
select dbo.GiveMeNewID(getdate()), @ParamB, @ParamC, @ParamD, @ParamE

Somehow, the GiveMeNewId gave back the same ID in two parallel requests.

This funcion is used at the moment in different places, inside one Stored Procedure and also from an "dynamic" SQL build from a "C# client".

I do not want to implement a "singleton" layer in some middle man software.

Any ideas on how to solve this issue? Thanks.

P.S: for testing I had the following lines inside the SQL function in order for me to run different the insert in different windows.

/*********************************************************/
declare @dtStart datetime=getutcdate()
while datediff(second,@dtStart,getutcdate()) < 10
begin
    set @dtStart = @dtStart
end
/*********************************************************/

Answer 1

Apart from handling exclusive access to the table itself as you mentioned you could also handle access to the function calls with sp_getapplock.

Example without the use of transactions, locking at the session level.

EXEC sp_getapplock @Resource = 'GiveMeNewID',   
               @LockMode = 'Exclusive',
               @LockOwner= 'Session';
insert into XXXX (A,B,C,D,E) 
select dbo.GiveMeNewID(getdate()), @ParamB, @ParamC, @ParamD, @ParamE;
EXEC sp_releaseapplock @Resource = 'GiveMeNewID',   
               @LockOwner= 'Session';

Or

DECLARE @date datetime
EXEC sp_getapplock @Resource = 'GiveMeNewID',   
               @LockMode = 'Exclusive',
               @LockOwner= 'Session';
select @date=dbo.GiveMeNewID(getdate())
EXEC sp_releaseapplock @Resource = 'GiveMeNewID',   
               @LockOwner= 'Session';
insert into XXXX (A,B,C,D,E) 
select @date, @ParamB, @ParamC, @ParamD, @ParamE;

---

Dummy example with transactions, locking at transaction level.

CREATE FUNCTION dbo.bla()
returns datetime2
as 
BEGIN
DECLARE @date datetime2 = getdate()
RETURN  @date
END

Run this query in one session (remember that a transaction will stay open)

BEGIN TRAN;  
DECLARE @result int;  
EXEC @result = sp_getapplock @Resource = 'GiveMeNewID',   
               @LockMode = 'Exclusive';  
SELECT dbo.bla()

Run the same in another session

BEGIN TRAN;  
DECLARE @result int;  
EXEC @result = sp_getapplock @Resource = 'GiveMeNewID',   
               @LockMode = 'Exclusive';  
SELECT dbo.bla()

The second session will be blocked until the transaction completes.

Don't forget to run COMMIT TRAN in both sessions.

---

Additionaly

Depending on the logic in the function, if you want to decrease the odds of duplicates when using the datetime data as a parameter, you could start using the datetime2 datetype and SYSDATETIME() function calls to get bigger time ranges.

Answer 2

In my test scenario I think I am able to solve this problem by writting:

  insert into XXXX with (tablock, xlock) (A,B,C,D,E) .....

At least now I am not able to replicate duplicated entries, but I am not sure if I am creating some problems where I am not aware.

If you think this is not a solution, please let me know.