Isolation Levels

The transaction isolation level specifies the transaction locking level for a connection. It determines the extent to which changes to data within a transaction are visible outside that transaction while uncommitted.

Table 16-1 lists and describes problems that might occur if several users access data concurrently without locking.

Table 16-1. Concurrency problems



Lost update

Two or more transactions select the same row and subsequently update the row. The transactions are unaware of each other and, as a result, updates overwrite one another, resulting in lost data.

Uncommitted dependency

(dirty read)

A second transaction selects a row that has been updated, but not committed, by another transaction. The data being read might be further updated or rolled back by the original transaction, resulting in invalid data in the second transaction.

Inconsistent analysis

(nonrepeatable read)

A second transaction reads different data each time the same row is read. The second transaction reads data that has been changed and committed by another transaction between the reads.

Phantom read

An insert or delete is performed for a row belonging to a range of rows being read by a transaction. The rows selected within the transaction are missing the newly inserted rows and contain deleted rows that no longer exist.

Locks ensure transactional integrity and maintain database consistency by controlling how resources can be accessed by concurrent transactions. ...

Get ADO.NET in a Nutshell now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.