SET TRANSACTION ISOLATION LEVEL <level name>
<level name> can be one of the following:
- READ COMMITTED
- READ UNCOMMITTED
- REPEATABLE READ
- No shared locks acquired
- No exclusive locks are honored
- Dirty Reads are possible
- Good for Performace
- Shared locks are held while data is read
- Avoids dirty reads
- Data can be changed after the shared lock is released but before the transaction completes. This can cause non-repeatable reads and phantom rows
- Default Isolation level used by Sql Server
- A copy of the data accessed by the transaction is maintained.
- Reduces blocking since other transactions can still be modifying the original data.
- Supported on SQL Server 2005 and beyond, but needs to be enabled manually.
- Shared locks are placed on all data that is used in a query.
- Prevents non-repeatable reads
- Phantom rows can still occur
- A range lock is placed on the data used. This prevents other transactions from updating or inserting data in that range.
- Removes phantom rows issue.
- Extremely bad for concurrency.
|Isolation Level||Dirty Reads||Repeatable Reads||Phantom Rows||Concurrency|