SQL Server : add row if doesn't exist, increment value of one column, atomic

Question

I have a table that keeps a count of user actions. Each time an action is done, the value needs to increase. Since the user can have multiple sessions at the same time, the process needs to be atomic to avoid multi-user issues.

The table has 3 columns:

• ActionCode as varchar
• UserID as int
• Count as int

I want to pass ActionCode and UserID to a function that will add a new row if one doesn't already exist, and set count to 1. If the row does exist, it will just increase the count by one. ActionCode and UserID make up the primary unique index for this table.

If all I needed to do was update, I could do something simple like this (because an UPDATE query is atomic already):

UPDATE (Table)
SET Count = Count + 1 
WHERE ActionCode = @ActionCode AND UserID = @UserID

I'm new to atomic transactions in SQL. This question has probably been answered in multiple parts here, but I'm having trouble finding those and also placing those parts in one solution. This needs to be pretty fast as well, without getting to complex, because these actions may occur frequently.

Edit: Sorry, this might be a dupe of MySQL how to do an if exist increment in a single query. I searched a lot but had tsql in my search, once I changed to sql instead, that was the top result. It isn't obvious if that is atomic, but pretty sure it would be. I'll probably vote to delete this as dupe, unless someone thinks there can be some new value added by this question and answer.

Answer 1

Assuming you are on SQL Server, to make a single atomic statement you could use MERGE

MERGE YourTable AS target
USING (SELECT @ActionCode, @UserID) AS source (ActionCode, UserID)
ON (target.ActionCode = source.ActionCode AND target.UserID = source.UserID)
WHEN MATCHED THEN 
    UPDATE SET [Count] = target.[Count] + 1
WHEN NOT MATCHED THEN   
    INSERT (ActionCode, UserID, [Count])
    VALUES (source.ActionCode, source.UserID, 1)
OUTPUT INSERTED.* INTO #MyTempTable;

UPDATE Use output to select the values if necessary. The code updated.

Answer 2

Using MERGE in SQL Server 2008 is probably the best bet. There is also another simple way to solve it.

If the UserID/Action doesn't exist, do an INSERT of a new row with a 0 for Count. If this statement fails due to it already being present (as inserted by another concurrent session just then), simply ignore the error.

If you want to do the insert and block while performing it to eliminate any chance of error, you can add some lock hints:

INSERT dbo.UserActionCount (UserID, ActionCode, Count)
SELECT @UserID, @ActionCode, 0
WHERE NOT EXISTS (
   SELECT *
   FROM dbo.UserActionCount WITH (ROWLOCK, HOLDLOCK, UPDLOCK)
   WHERE
      UserID = @UserID
      AND ActionCode = @ActionCode
);

Then do the UPDATE with + 1 as in the usual case. Problem solved.

DECLARE @NewCount int,

UPDATE UAC
SET
   Count = Count + 1,
   @NewCount = Count + 1
FROM dbo.UserActionCount UAC
WHERE
   ActionCode = @ActionCode
   AND UserID = @UserID;

Note 1: The MERGE should be okay, but know that just because something is done in one statement (and therefore atomic) does not mean that it does not have concurrency problems. Locks are acquired and released over time throughout the lifetime of a query's execution. A query like the following WILL experience concurrency problems causing duplicate ID insertion attempts, despite being atomic.

INSERT T
SELECT (SELECT Max(ID) FROM Table) + 1, GetDate()
FROM Table T;

Note 2: An article I read by people experienced in super-high-transaction-volume systems said that they found the "try-it-then-handle-any-error" method to offer higher concurrency than acquiring and releasing locks. This may not be the case in all system designs, but it is at least worth considering. I have since searched for this article several times (including just now) and been unable to find it again... I hope to find it some day and reread it.