READ UNCOMMITTED isolation level behavior on more than one transaction

Question

I have a stored procedure to update a table, and i set the isolation level to READ UNCOMMITTED. In this sp i set the comment count (CommentCount+=1). If more than one user call this sp at the same time, is it possible that comment count increase less than number of user that added comment?

Answer 1

SQL Server still acquires locks on updated rows in the READ UNCOMMITTED isolation level. An UPDATE statement like this will not miss increments when executed by multiple READ UNCOMMITTED sessions concurrently:

UPDATE dbo.Post
SET CommentCount += 1
WHERE PostID = @PostID;

Here's a trace of locks by this statement, updating by a clustered primary key on PostID. The exclusive lock will block other concurrent updates to the row.

+---------------+----+--------------+----------+--+
| Lock:Acquired | 58 | 5 - OBJECT   | 8 - IX   |  |
| Lock:Acquired | 58 | 6 - PAGE     | 8 - IX   |  |
| Lock:Acquired | 58 | 7 - KEY      | 5 - X    |  |
| Lock:Released | 58 | 7 - KEY      | 0 - NULL |  |
| Lock:Released | 58 | 6 - PAGE     | 0 - NULL |  |
| Lock:Released | 58 | 7 - KEY      | 5 - X    |  |
| Lock:Released | 58 | 6 - PAGE     | 8 - IX   |  |
| Lock:Released | 58 | 5 - OBJECT   | 8 - IX   |  |
+---------------+----+--------------+----------+--+

And here's a trace where the row is located using a non-clustered primary key index. The update lock on the non-clustered key will serialize other update statements for the same key and the exclusive lock on the clustered key will block other data modifications.

+---------------+----+------------+----------+--+
| Lock:Acquired | 58 | 5 - OBJECT | 8 - IX   |  |
| Lock:Acquired | 58 | 6 - PAGE   | 7 - IU   |  |
| Lock:Acquired | 58 | 7 - KEY    | 4 - U    |  |
| Lock:Acquired | 58 | 6 - PAGE   | 7 - IU   |  |
| Lock:Acquired | 58 | 7 - KEY    | 4 - U    |  |
| Lock:Acquired | 58 | 6 - PAGE   | 8 - IX   |  |
| Lock:Acquired | 58 | 7 - KEY    | 5 - X    |  |
| Lock:Released | 58 | 7 - KEY    | 0 - NULL |  |
| Lock:Released | 58 | 6 - PAGE   | 0 - NULL |  |
| Lock:Released | 58 | 7 - KEY    | 4 - U    |  |
| Lock:Released | 58 | 6 - PAGE   | 7 - IU   |  |
| Lock:Released | 58 | 7 - KEY    | 5 - X    |  |
| Lock:Released | 58 | 6 - PAGE   | 8 - IX   |  |
| Lock:Released | 58 | 5 - OBJECT | 8 - IX   |  |
+---------------+----+------------+----------+--+