Updating number of article views - potential concurrent access issue? I have articles on my website (built in PHP) and when an article is viewed the number of views is recorded back in the database. The SQL code snippet of my load method is:

SELECT *
FROM article
WHERE id = :id;
UPDATE article
SET views = views + 1
WHERE id = :id;


This works fine, but I've only tested it locally with one user - me.

What happens if hundreds of people tried accessing the same article at once? I.e. would the update slow things down considerably? If so, is there a better way of doing this?
The performance with simple queries such as yours will depend more on what is going on in the database than what is going on in your code.

Here are a few things to consider if you are maintaining the DB yourself, or to bring up with the DBA if you are not:


If the id field is an integer, and ideally a  surrogate key (e.g. generated by an identity function instead of being a natural key using date/time, articlename, etc.), that will perform significantly better. Integers can be easily and swiftly ordered and generate relatively small indexes that can be searched quickly. A surrogate key improves performance for many reasons, one of which improved index performance by pushing data INSERTs to the leading edge of the index.
If the article table has a clustered index on the id field, that's even better for your performance as it will reduce the cost of both the SELECT and the UPDATE in most cases. The clustered attribute means that the data is already ordered by your ID column, significantly improving search performance.
If you don't have a lot of competing queries that try to lock bigger sets of data than a row at a time, that will also be good for the performance of these queries. You want to avoid contention for locking the same data as much as possible. For example, you don't want a big 100,000-row update that runs every 15 minutes competing with your single-row update.


Which brings me to the one thing I would change about your query: locking. Unless you really need per-transaction integrity when serving articles to the readers (and in most cases, for a publishing system, you probably don't) you can hint to the database that you'd like a more permissive transaction isolation level. In layman's terms, you can volunteer to let the other guy win if you both need a lock.

So if you are doing a SELECT at the same time an UPDATE is occurring to the same article, you can allow a READ UNCOMMITTED transaction isolation level on your query to avoid taking unnecessary locks. The syntax for doing this varies from one DBMS to another, but usually it's some variation on the theme of SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. Some (MS SQL, for example) also have a shortened syntax such as WITH (NOLOCK).

Finally, a couple of caveats to this advice since you didn't specify your DB system. Some DB systems don't fit this mold. For example, Oracle implements non-blocking SELECTs without requiring dirty reads, so there's no equivalent to NOLOCK there because it's a built-in assumption. MySQL, on the other hand, has InnoDB and MyISAM tables, and on the latter type you are not going to be able to use READ UNCOMMITTED due to the underlying architecture of the table. However, even though you will be taking a table lock the architecture is designed to efficiently allow many concurrent reads by sharing the lock to multiple readers.

To sum up, your queries look fine but explore whether a NOLOCK-type option is available.