When locking is used as the concurrency control mechanism, it solves concurrency problems. This allows all transactions to run in complete isolation of one another, although there can be more than one transaction running in Microsoft® SQL Server™ at any one time.
Serializability is the property such that the database state achieved by running a set of concurrent transactions is equivalent to the database state that would be achieved if the set of transactions were executed serially in some order.
Although serialization is important to transactions to ensure that the data in the database remains correct and meaningful, many transactions do not always require full isolation. For example, several writers are working on different chapters of the same book. New chapters can be submitted to the project at any time. However, after a chapter has been edited, a writer cannot make any changes to the chapter without the editor’s approval. In this way, the editor can be assured of the accuracy of the book project at any point in time, despite the arrival of new unedited chapters. This is an example of the read committed isolation level: The editor can see both previously edited chapters and any new, recently submitted chapters.
The level at which a transaction is prepared to accept inconsistent data is termed the isolation level. It is the degree to which one transaction must be isolated from other transactions. A lower isolation level increases concurrency but at the expense of data correctness. Conversely, a higher isolation level ensures that data is correct, but can negatively affect concurrency. The isolation level required by an application determines the locking behavior SQL Server uses.
SQL-92 defines four isolation levels, all of which are supported by SQL Server:
If transactions are run at an isolation level of serializable, then any concurrent overlapping transactions are guaranteed to be serializable.
The four isolation levels allow different types of behavior.
| Isolation level | Dirty read | Nonrepeatable read | Phantom |
|---|---|---|---|
| Read uncommitted | Yes | Yes | Yes |
| Read committed | No | Yes | Yes |
| Repeatable read | No | No | Yes |
| Serializable | No | No | No |
Transactions must be run at an isolation level of repeatable read or higher to prevent lost updates that can occur when two transactions each retrieve the same row, and then later update the row based on the originally retrieved values. If the two transactions update rows using a single UPDATE statement and do not base the update on the previously retrieved values, lost updates cannot occur at the default isolation level of read committed.