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

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:

  • can I give SQL Server database name with hyphen like abc-123?
  • SQL Server CTE hierarchy issue
  • Possible steps to improve SQL Server query performance
  • SQL Server: Get data from record with max date / newest date
  • SQL: search for a string in every varchar column in a database
  • What is the practical use of timestamp column in sql server with example?
    • 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.

    3 Solutions collect form web for “SQL Server : add row if doesn't exist, increment value of one column, atomic”

    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.

    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.

    Incase anyone else needs the syntax to use this in a stored procedure and return the inserted/updated rows (I was surprised inserted.* also returns the updated rows, but it does). Here is what I ended up with. I forgot I had an additional column in my primary key (ActionKey), it is reflected below. Can also do “output inserted.Count” if you only want to return the Count, which is more practical.

    CREATE PROCEDURE dbo.AddUserAction
    (
    @Action varchar(30),
    @ActionKey varchar(50) = '',
    @UserID int
    )
    AS
    
    MERGE UserActions AS target
    USING (SELECT @Action, @ActionKey, @UserID) AS source (Action, ActionKey, UserID)
    ON (target.Action = source.Action AND target.ActionKey = source.ActionKey AND target.UserID = source.UserID)
    WHEN MATCHED THEN 
        UPDATE SET [Count] = target.[Count] + 1
    WHEN NOT MATCHED THEN   
        INSERT (Action, ActionKey, UserID, [Count])
        VALUES (source.Action, source.ActionKey, source.UserID, 1)
    output inserted.*;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.