Comment by sirclueless | Hacker News Reader

Comment by sirclueless | Hacker News Reader

Parent Full thread

Doesn't this have the same problems as the DELETE+INSERT from OP? Namely, a concurrently executing transaction will see a brief moment in time where no valid (e.g. CreatedOn < NOW() < InvalidatedOn) UserProfile exists.

There's also a bunch of weird corner cases you need to expect. For example, you can have multiple valid UserProfile objects at once if two transactions insert concurrently. If you LIMIT 1 and sort by CreatedOn that may not be a huge issue, but it will look weird if you try to look at historical data.

If you execute this in a transaction (as you should whenever dealing with any two queries that alter state and logically follow each other), then neither case creates a state in which some other transaction sees the first half of this sort of update complete without the second half. And if you don't, then it's not quite the same as DELETE+INSERT, because you haven't destroyed any data.

Also, even if one considered this "no better" than DELETE+INSERT, it has significant other benefits for other purposes. The history of data is worth it alone.

But you've made me consider the issue more, and I think perhaps a better way to run this would be:

    INSERT ... -- same as before
    
    DECLARE @ID int;
    SELECT @ID = SCOPE_IDENTITY();
    
    UPDATE the_table
    SET InvalidatedOn = CASE WHEN the_table.ID = @ID THEN MAX_DATE() ELSE NOW() END
    WHERE surrogate_key = @KEY
    AND InvalidatedOn > NOW();

then, even if you intentionally created a race condition, you should always end up with one and only one "definitive" record.

Reply on news.ycombinator.com