A dirty read occurs when a transaction reads data that is being changed by another transaction that has not yet been committed or rolled back; the data read cannot really be trusted. For example, let's suppose you have a transaction in which you SELECT data from a table and specify of range of valueslet's say last names between the letters K through M. If an uncommitted transaction is changing a name in that range and your SELECT statement were to read the changed data, your transaction would be performing a dirty read.
The lowest isolation level, READ UNCOMMITTED
, permits dirty reads. If your transaction is in the READ UNCOMMITTED
isolation level, then your SELECT
statement does not take any shared locks on the data in order to read it, and so it is not blocked by any transaction that has an exclusive lock on the data because it is changing it.
The next higher isolation level, READ COMMITTED, prevents dirty reads. If your transaction is in the READ COMMITTED isolation level, which is the SQL Server 2000 and SQL Server 2005 default, your SELECT statements will attempt to take shared locks on the data to be read, and your statement will be blocked by another transaction changing the data. Your transaction will be temporarily blocked because shared locks are not compatible with exclusive locks. After the other transaction is finished and either commits or rolls back, your SELECT statement will execute and you will only read the committed data.
However, in the READ COMMITTED isolation level, you are not guaranteed to see the same data at any time in the transaction. If at the beginning of the transaction, your SELECT statement of the range of values between K and M resulted in 1,000 rows, you could issue the same SELECT statement later and perhaps see fewer rows if some were deleted, more rows if some others were inserted, and the data itself could be changed. In the READ COMMITTED isolation level, SQL Server releases its shared locks on reads after finishing the SELECT statement, so other transactions may change the data.
A non-repeatable read occurs if you read data at one point in a transaction, and then later re-read the data, and the data is changed. The next higher isolation level, REPEATABLE READ, prevents non-repeatable reads. In the REPEATABLE READ isolation level, your transaction is guaranteed that any data you read will not change until your transaction is finished. If your SELECT statement at the beginning of your transaction resulted in 1,000 rows in the range between K and M, then at any time later in your transaction, if you re-issued the same SELECT statement, you will still see all 1,000 rows unchanged. The data you initially read will remain unchanged, eliminating non-repeatable reads.
SQL Server accomplishes the REPEATABLE READ isolation level by holding all shared locks that your transaction acquires until the end of your transaction. Every transaction, regardless of isolation level, already holds all exclusive locks until you end the transaction with a COMMIT or ROLLBACK; the REPEATABLE READ isolation level just adds your shared locks to the mix, keeping them until you end the transaction.
The REPEATABLE READ isolation level does not prevent phantoms: new rows that may be inserted into a range. For example, in a REPEATABLE READ you could issue your SELECT statement for values in the range of K through M and get 1,000 rows, and then later get 1,001 rows as a new row is inserted. Your transaction is still not completely isolated.
The strongest type of isolation level, SERIALIZABLE, ensures that all data that your transaction reads in a certain range of values will remain unchanged until the end of your transaction. Suppose your transaction is in the SERIALIZABLE isolation level. At the beginning of the transaction you issue a SELECT statement on the range of values of K through M and get 1,000 rows. The SERIALIZABLE isolation level guarantees that at any other time during your transaction, the same SELECT statement will result in exactly the same set of 1,000 rows, and they will be unchanged.
SQL Server 2000 and 2005 accomplish the SERIALIZABLE isolation level by using key-range locks, which are special shared locks that prevent inserts of new rows into a range of values. In the SERIALIZABLE isolation level, your transaction will behave as though it was the only transaction on the system.