Incremented DB Field

Question

Let's say that I have an article on a website, and I want to track the number of views to the article. In the Articles table, there's the PK ID - int, Name - nvarchar(50), and ViewCount - int. Every time the the page is viewed, I increment the ViewCount field. I'm worried about collisions when updating the field. I can run it in a sproc with a transaction like:

CREATE PROCEDURE IncrementView
(
    @ArticleID int
)
as

BEGIN TRANSACTION

UPDATE Article set ViewCount = ViewCount + 1 where ID = @ArticleID

IF @@ERROR <> 0
BEGIN
    -- Rollback the transaction
    ROLLBACK

    -- Raise an error and return
    RAISERROR ('Error Incrementing', 16, 1)
    RETURN
END

COMMIT

My fear is that I'm going to end up with PageViews not being counted in this model. The other possible solution is a log type of model where I actually log views to the articles and use a combination of a function and view to grab data about number of views to an article.

Answer 1

Probably a better model is to cache the number of views hourly in the app somewhere, and then update them in a batch-style process.

-- Edit:

To to elaborate more, a simple model for you may be:

1. Each page load, for the given page, increment a static hashmap. Also on each load, check if enough time has elapsed since 'Last Update', and if so, perform an update.

2. Be tricky, and put the base value in the asp.net cache (http://msdn.microsoft.com/en-us/library/aa478965.aspx) and, when it times out, [implement the cache removal handler as described in the link] do the update. Set the timeout for an hour.

In both models, you'll have the static map of pages to counts; you'll update this each view, and you'll also use this - and the cached db amount - to get the current 'live' count.

Answer 2

The database should be able to handle a single digit increment atomically. Queries on the queue should be handled in order in the case where there might be a conflict. Your bigger issue, if there is enough volume will be handling all of the writes to the same row. Each write will block the reads and writes behind it. If you are worried, I would create a simple program that calls SQL updates in a row and run it with a few hundred concurrent threads (increase threads until your hardware is saturated). Make sure the attempts = the final result.

Finding a mechanism to cache and/or perform batch updates as silky suggests sounds like a winner.

Jacob

Answer 3

You don't need to worry about concurrency within a single update statement in SQL Server.
But if you are worried about 2 users hitting a table in the same tenth of a second, keep in mind that there are 864,000 10th of a seconds in a day. Doesn't sound like something that is going to be an issue for a page that serves up articles.

Answer 4

Have no fear!

This update is a single (atomic) transaction - you cannot get 'collisions'. Even if 5,000,000 calls to IncrementView all hit the database at the exact same moment, they will each be processed in a serial, queue like fashion - thats what you are using a database engine for - consistency. Each call will gain an exclusive update lock on the row (at least), so no subsequent queries can update the row until the current one has committed.

You don't even need to use BEGIN TRAN...COMMIT. If the update fails, there is nothing to rollback anyway.

I don't see the need for any app caching - there's no reason why this update would take a long time adn therefore should have no impact on the performance of your app. [Assuming it's relatively well designed!]