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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Thread safe counter in sql

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 + 1
OUTPUT INSERTED.count
WHERE event = @event

You 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 + 1
OUTPUT INSERTED.count, @event INTO YourEventsTable([count],[Event])
OUTPUT INSERTED.count
WHERE event = @event
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-24 : 15:42:28
[code]MERGE dbo.MyTable AS tgt
USING (
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"
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -