Update activity time on concurrent connections

Question

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)

What RDBMS and version? Many have a MERGE statement. The other thing to do would key the table by the combination of user id and timestamp (log each row separately, don't update them - although table may get large). Or just cache the information in your application, and write to the DB if you don't see them for a while (DB writes are slow by comparison). — Clockwork-Muse
– Clockwork-Muse, Commented Jun 11, 2015 at 8:23
You have a composite primary key. It needs to change if you want to do it like this. That is the first thing. The second is to use a MERGE instead of the if statement. — TehBoyan
– TehBoyan, Commented Jun 11, 2015 at 8:33
ok, but if i remove the composite primary key, i will get a lot of rows with the same information? We even save the online time in this table for the user so we can se, each day, how long a specific user has been online. — mrcode
– mrcode, Commented Jun 11, 2015 at 8:50

Clockwork-Muse · Accepted Answer · 2015-06-11 08:51:24Z

1

You're in luck! Azure SQL uses SQL Server, which supports MERGE:

MERGE User_Activity AS target
USING (SELECT @UserNr, GETUTCDATE()) AS source (userNr, rightNow)
      ON (target.userId = source.userNr)
WHEN MATCHED THEN 
     UPDATE SET end_date = source.rightNow
WHEN NOT MATCHED THEN
     INSERT (user_id, stat_date, start_date, end_date)
            VALUES (source.userNr, source.rightNow, source.rightNow, source.rightNow)

(not tested - no sample data)

Note, though this solves this particular issue, I agree with @The Bojan - you have a table with a multi-part key. Probably what you should do is change your table to look something like this:

CREATE TABLE User_Activity (userId INTEGER,
                            lastSeenAt DATETIME2)

... and just INSERT to it (and don't use any transactions). At the end of each day you can easily roll up all the records into your current table, then clear it out.

answered Jun 11, 2015 at 8:51

Clockwork-Muse

13.2k6 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mrcode Over a year ago

Thanx, works perfect. But the best way is maybe to save each request and the day after calculate the statistics for the user?

Clockwork-Muse Over a year ago

@mrcode - Maybe, you'd need to do testing. At minimum, it would enable you to not need transactional locks on the table (and if each user is spawning lots of concurrent entries, that could be a bottleneck). Adding 1.5 mil rows to a table is nothing, especially because you won't need an index for it. Writing to a db is slow (comparatively) - you may also want to try buffering and writing in bulk. Or keeping the table in memory/application code. In general, updating aggregates on-the-fly is more problematic than building it off of the complete stats.

Zhertal · Accepted Answer · 2015-06-11 08:19:43Z

0

Consider using Transactions in your SP to avoid the SQL instructions to be mixed up between concurrent executions and to ensure the check from your IF statement is getting the correct data for its check everytime

answered Jun 11, 2015 at 8:19

Zhertal

4251 gold badge4 silver badges10 bronze badges

3 Comments

Clockwork-Muse Over a year ago

Yes, but this would require complete serialization of (writes to) the table. Expensive, and a major bottleneck.

Zhertal Over a year ago

What would be a better solution for this, then?

Clockwork-Muse Over a year ago

depends on RDBMS, and intended table use. Constantly updating the same row in the table is likely to be a large pain regardless.

Collectives™ on Stack Overflow

Update activity time on concurrent connections

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related