Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
xalpha
Starting Member
13 Posts |
Posted - 2013-03-24 : 10:37:14
|
Hello,I want to design a thread safe counter with a sql table. The table has only two columns "event" and "count". The application simply makes a query to increase the counter of a event by one. Here is my solution:UPDATE mytable SET count = count + 1OUTPUT INSERTED.countWHERE event = @eventYou might say this is a really simple task but now the real challenge is coming. The example is safe to be called from different threads without problems. But the insert for a new event is missing. If a add something to allow an automatic insert I pay with the thread problem.Have I really to lock the table to get this running in a proper way?Thank you for a touch of genius. |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-24 : 13:08:09
|
You can use output clause to insert into the events table as well - that is thread-safe:UPDATE mytable SET count = count + 1OUTPUT INSERTED.count, @event INTO YourEventsTable([count],[Event])OUTPUT INSERTED.countWHERE event = @event |
|
|
xalpha
Starting Member
13 Posts |
Posted - 2013-03-24 : 14:34:21
|
Thank you. Now I realize that my description was not quite good. mytable is the EventsTable. I only search for a thread-safe extension from update to a update or insert scenario. Most of the time a only make a update of mytable but if a new event occurs I have to do an insert of the event and the count value 1. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-03-24 : 14:55:06
|
I am afraid I did not follow your description. I understood that you have one table which stores event id and count. And, that you want to insert into that table or increment the counter for a given EventId. But, I am not clear on where the issue of thread safety comes into play because whether you insert or update, it is one statement which is atomic. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-03-24 : 15:42:28
|
[code]MERGE dbo.MyTable AS tgtUSING ( VALUES (@Event, 1) ) AS src([Event], [Count]) ON src.[Event] = tgt.[Event]WHEN MATCHED THEN UPDATE SET tgt.[Count] += src.[Count]WHEN NOT MATCHED BY TARGET THEN INSERT ( [Event], [Count] ) VALUES ( src.[Event], src.[Count] )OUTPUT inserted.[Count];[/code] N 56°04'39.26"E 12°55'05.63" |
|
|
xalpha
Starting Member
13 Posts |
Posted - 2013-03-30 : 08:40:57
|
@James_K:You are right normally it should be thread-safe. But over the time I have had so many problems with different threads. Now I try to make everything double-save...@SwePeso:Thank you very much for this solution. I just played around with my test environment and everything worked perfectly. It is a really nice an proper query. |
|
|
|
|
|