1

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
/*********************************************************/
3
  • @RandiVertongen the function is something that I can not share public... but the ideia is: if I run the function many times the result should be always the same as long as an insert has not changed the base data. Example: if I make 2 inserts in a row, the Id of the second one should be sequential to the previous one.
    – Dryadwoods
    Feb 10, 2020 at 13:09
  • Alright, any more info on how getdate() is transformed to an id? It will be hard to know why the same IDwas returned by concurrent calls to the function without having the actual function definition or something that has the same logic. You could look into stuff like sp_getapplock See here to disable concurrent access to the function. Feb 10, 2020 at 13:13
  • @RandiVertongen the ID is a combination of letters and numbers, but also the COUNT (filtered) result on the table in which the insert is made (). This is why the values should not change if there is no insert, and should change as soon as one insert is executed.... but somehow, I got 2 inserts with the same value... and I think that this happens because the function might take some miliseconds to return the value, and if 2 inserts try to call the function in parallel, both will get the same result from the function. I am thinking if there is a pretty way to lock the insert and table.
    – Dryadwoods
    Feb 10, 2020 at 13:19

2 Answers 2

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.

2
  • Thanks, that is also an alternative, but i need to see if I am able to use transactions in every places I will need to change my code. Meanwhile, I will accept you answer as the correct one.
    – Dryadwoods
    Feb 10, 2020 at 13:33
  • @Dryadwoods Thanks for the feedback! Yours can work too. You can run sp_getapplock without transactions btw, adding an edit. Feb 10, 2020 at 13:37
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.