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?
READ UNCOMMITTED isolation level behavior on more than one transaction
252 views Asked by Meysam Khoshbakht At
1
SQL Server still acquires locks on updated rows in the
READ UNCOMMITTED
isolation level. AnUPDATE
statement like this will not miss increments when executed by multipleREAD UNCOMMITTED
sessions concurrently: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.
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.