When You Might See SQL Server Concurrency Problems

If locking isn’t used, and thus no isolation exists between transactions, the following four concurrency problems may appear:

•     Lost update
•     Dirty reads (discussed earlier, in the “Locking” section)
•     Nonrepeatable reads
•     Phantoms

The lost update concurrency problem occurs when no isolation is provided to a transaction from other transactions. This means that several transactions can read the same data and modify it. The changes to the data by all transactions, except those by the last transaction, are lost.

The nonrepeatable read concurrency problem occurs when one process reads data several times, and another process changes the same data between two read operations of the first process. Therefore, the values read by both read operations of the first process are different.

The phantom concurrency problem is similar to the nonrepeatable read concurrency problem, because two subsequent read operations can display different values, but in this case, the reason for this behavior lies in the different number of rows being read the first time and the second time. (Additional rows, called phantoms, are inserted by other transactions.)