SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Thread safe counter in sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xalpha
Starting Member

13 Posts

Posted - 03/24/2013 :  10:37:14  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 03/24/2013 :  13:08:09  Show Profile  Reply with Quote
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 - 03/24/2013 :  14:34:21  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 03/24/2013 :  14:55:06  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 03/24/2013 :  15:42:28  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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];



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

xalpha
Starting Member

13 Posts

Posted - 03/30/2013 :  08:40:57  Show Profile  Reply with Quote
@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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000