I’m really puzzled.
I am in the ReadCommitted Isolation level with a transaction. I’m also changing a counter value in it, which looks something like this:
Update tblCount set counter = counter + 1
This transaction occurs to take place pretty regularly and concurrently in my desktop application. Recently, we discovered a problem where the counter value occasionally is either missing or not updated. In order to be sure that records have been entered, we add one record for each counter update. However, sometimes the counter does not update. One in every 2000 simultaneous transactions results in this.
I sincerely doubt that the problem I’m seeing is a lost update anomaly, but if you look at the command above, it just updates the counter using its own value. If I had begun a transaction and it had reached this sentence, it should have locked the row. This shouldn’t result in missing updates, yet somehow it is.
Is it true that the update command functions in two stages? In the same way that it first reads the counter value (during which it does not receive the exclusive lock) and then writes the newly computed value (during which it receives the exclusive lock)? Please assist me; I’m completely lost.