I have a api and on each request to this api i save/update the user activity.
But the problem is, when there is 5 concurrent connections to the api from the same user, i get this:
Violation of PRIMARY KEY constraint 'PK_user_act'. Cannot insert duplicate key in object 'user_activity
The duplicate key value is (2015-06-11, 76146, 1). The statement has been terminated.*
I have this sql:
if not exists (select 1 from user_activity where user_id = @UserNr and stat_date = CAST(GETUTCDATE() AS DATE)) insert into user_activity(user_id, stat_date, start_date, end_date) VALUES (@UserNr, GETUTCDATE(), GETUTCDATE(), GETUTCDATE()) ELSE UPDATE user_activity set end_date = GETUTCDATE() where user_id = @UserNr and stat_date = CAST(GETUTCDATE() AS DATE)
I use ADO.NET and a SP to update the user activity. I think this sql is running at the same time, and the first request is saved, and the other 4 get this error.
What can i do to fix this?
UPDATE: We using Azure SQL, v12 for this. (We have 1-1,5 m connections to this api / day)