Tutorial by Examples

SQL Server 2008 R2 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED This is the most permissive isolation level, in that it does not cause any locks at all. It specifies that statements can read all rows, including rows that have been written in transactions but not yet committed (i.e., they are...

SQL Server 2008 R2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED This isolation level is the 2nd most permissive. It prevents dirty reads. The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT: If set to OFF (the default setting) the transaction uses shared l...

Dirty reads (or uncommitted reads) are reads of rows which are being modified by an open transaction. This behavior can be replicated by using 2 separate queries: one to open a transaction and write some data to a table without committing, the other to select the data to be written (but not yet com...

SQL Server 2008 R2 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ This transaction isolation level is slightly less permissive than READ COMMITTED, in that shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes, as opposed to b...

SQL Server 2008 R2 SET TRANSACTION ISOLATION LEVEL SNAPSHOT Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction, i.e., it will only read data that has been committed prior to the transa...

SQL Server 2008 R2 SET TRANSACTION ISOLATION LEVEL SERIALIZEABLE This isolation level is the most restrictive. It requests range locks the range of key values that are read by each statement in the transaction. This also means that INSERT statements from other transactions will be blocked if the...