9

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.

Community
  • 1
  • 1
eselk
  • 6,764
  • 7
  • 60
  • 93

3 Answers3

10

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.

Serge Belov
  • 5,633
  • 1
  • 31
  • 40
  • Although it's attractive, that syntax will not work for most databases. Better find out first what db OP is using – Bohemian Dec 03 '12 at 23:13
  • Thank you for adding the example. I might go with something like this over the other post I linked because I also would like to return the final value/count and have that part be atomic as well. Nice to have options at the very least, so I think I will leave this open (not delete or report dupe). – eselk Dec 03 '12 at 23:15
  • @eselk To get the values atomically, just use `OUTPUT` clause. See the updated answer. – Serge Belov Dec 03 '12 at 23:19
  • Accepted answer because I've done enough testing to know this will work for me. I just haven't figured out how to return MyTempTable.Count from my stored procedure yet. It does insert/update the row correctly though. – eselk Dec 04 '12 at 00:45
  • In the long run I was hooking all of this up to an ASP.NET typed dataset, and just using "OUTPUT INSERTED.*" without the INTO #MyTempTable was the format that worked best. Does the update then returns the rows so I can work with them in the typed dataset object (read the Count value). – eselk Dec 04 '12 at 01:06
  • 1
    `WITH (HOLDLOCK)` hint is needed to avoid race conditions http://weblogs.sqlteam.com/dang/archive/2009/01/31/UPSERT-Race-Condition-With-MERGE.aspx – Mikhail Shilkov Apr 18 '17 at 14:03
3

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.

ErikE
  • 48,881
  • 23
  • 151
  • 196
  • The question is about atomic transactions, so at the very least you 'd need to wrap up these statements into `BEGIN TRAN` and `END TRAN` and make sure the isolation level is appropriate. In which case you might want check before inserting instead of relaying on the error anyway. – Serge Belov Dec 03 '12 at 23:37
  • @SergeBelov I suppose that if one wanted the INSERT to rollback if the UPDATE fails, then yes, one would have to use BEGIN TRAN and END TRAN. The isolation level is not really at issue because you can't perform an INSERT or UPDATE without appropriate locks, no matter what the isolation level is. To acquire appropriate locks on the INSERT statement so no error will occur, locks close to what I updated my answer with will do the trick. – ErikE Dec 04 '12 at 01:32
  • @SergeBelov Remember that my initial answer did not intend to use any blocking. If you need blocking, `SERIALIZABLE` corresponds to `HOLDLOCK`, but it is not enough by itself! I prefer not to write code that combines different ways of specifying locking in the same query (`SET TRANSACTION ISOLATION LEVEL` vs. `WITH (LOCKHINT)`). If I need HOLDLOCK for a particular query, I put the hint on the query. I wasn't trying to say that isolation level is meaningless, but that it was insufficient for the purposes here and thus not really the most important issue. – ErikE Dec 04 '12 at 01:58
  • [MSDN](http://msdn.microsoft.com/en-us/library/ms187373.aspx): "`HOLDLOCK` ... Is equivalent to `SERIALIZABLE`", "the same semantics as a transaction running at the `SERIALIZABLE` isolation level". – Serge Belov Dec 04 '12 at 01:58
  • OK, the last comment referred to an edited one, which is now irrelevant. – Serge Belov Dec 04 '12 at 02:05
  • Sorry about that @SergeBelov. I realized I shouldn't be lazy and deleted my comment before you replied. – ErikE Dec 04 '12 at 02:27
  • Thanks for all of the additional info. I do think your original simple idea would also have worked, but the merge is designed for this, and I'm lucky enough to be using SQL 2008. As far as the try-it-then-handle-any-error, I know that would be faster for the ISAM database engine I wrote myself, but that's likely a pretty unique case :) – eselk Dec 04 '12 at 05:17
1

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.*;
eselk
  • 6,764
  • 7
  • 60
  • 93