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:
I want to pass
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.
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.*;