Atomic SQL Operation: unsure of approach (insert/update depending on existing data)

Question

A table contains something like the following:

   ID: 4 
 DATE: 040910 
COUNT: 42

If the ID and DATE already exist in the table, increase the count by one. If the specific ID and DATE do not already exist, create with a count of 1. 'ID' is not unique - it may be duplicated in the table as long as the date code changes.

Everytime I think of a solution, it seems to incur possible race conditions. I thought about BEGIN/END transaction, but that doesn't seem 100% reliable either. I can't just do the insert and if it fails then update unless I merge the ID field into DATE and make it a unique column.

Ideas?

Edit: I've just thought of this:

INSERT INTO table (...) VALUES (...)
WHERE NOT EXISTS (SELECT 1 FROM table WHERE table.id == x AND table.date == y)

UPDATE table SET count = count + 1 WHERE table.id == x AND table.date == y

So the update is always executed, but the insert depends on the current value of the table, but I'm concerned that still 2 records could appear with the same id & date.

You can also use **MERGE**: http://msdn.microsoft.com/en-us/library/bb522522.aspx — ypercubeᵀᴹ, Sep 04 '11 at 21:16
possible duplicate of [Only inserting a row if it's not already there](http://stackoverflow.com/questions/3407857/only-inserting-a-row-if-its-not-already-there) — Martin Smith, Sep 04 '11 at 21:19

NullUserException · Accepted Answer · 2011-09-04T21:20:14.687

2

What is wrong with transactions? Unless there's a bug in SQL Server, they will work as described. You can also set the ISOLATION LEVEL of a transaction to suit your needs.

BEGIN TRANSACTION;

UPDATE YourTable
   SET Count = Count + 1
 WHERE ID = X AND Date = Y;

IF @@rowcount = 0
    INSERT INTO YourTable (ID, Date, Count)
    VALUES (X, Y, 1);

COMMIT TRANSACTION;

You should also have a unique constraint on both columns (ie: UNIQUE(ID, DATE))

edited Sep 04 '11 at 21:20

answered Sep 04 '11 at 21:11

NullUserException

83,810
28
209
234

What happens if two transactions try to INSERT at (about) the same time the same `(ID,DATE,1)` combination ? – ypercubeᵀᴹ Sep 04 '11 at 21:17
@Martin Hmmm. Assuming I don't have the `UNIQUE` constraint, would this still be a problem if I set the transaction's isolation level to serializable? (deadlocks notwithstanding) – NullUserException Sep 04 '11 at 21:24
I think that in that case either two rows will be inserted or (if a UNIQUE constraint exists), one INSERT will fail. – ypercubeᵀᴹ Sep 04 '11 at 21:28
@Martin I am also just throwing serializable out there, but maybe just `REPEATABLE READ` will be enough. You can't get away from the fact that for this to work, some locking will have to occur. – NullUserException Sep 04 '11 at 21:37
Repeatable Read definitely wouldn't work in the absence of the unique constraint. In the case that the row doesn't exist nothing exists to get locked. You need some exclusive lock on the range where the row would exist. As far as I can see `serializable` works fine in that the updates block each other so there is no deadlock risk and as long as there is an index on `ID, Date` there shouldn't be undue blocking either AFAIK. Was sure there was some reason why explicit `HOLDLOCK, UPDLOCK, ROWLOCK` was better but if so can't think of it now... – Martin Smith Sep 04 '11 at 22:06
Added a unique constraint on both columns. Not sure if the above would work (possible race condition where two clients both did not see the update and attempted the insert, one of which failed, unless you use serializable). Seems overly complicated for a simple operation, so I now do the INSERT first with COUNT = 0 and then an UPDATE. If the INSERT fails who cares, the following UPDATE still works, so nothing lost. Hopefully. – squidge Sep 05 '11 at 12:52

Atomic SQL Operation: unsure of approach (insert/update depending on existing data)

1 Answers1