You are the database administrator of your company. You are in the process of configuring transaction isolation level to define the locking and row versioning behavior of Transact-SQL statements. You want to ensure that phantom reads do not occur when you configure the transaction isolation level. Which two transaction isolation levels can you configure to accomplish this? (Choose two. Each correct answer represents a complete solution.)
A.
READ UNCOMMITTED
B.
READ COMMITTED
C.
REPEATABLE READ
D.
SNAPSHOT
E.
SERIALIZABLE
Explanation:
You can configure the SNAPSHOT or SERIALIZABLE isolation level. Only these two isolation levels prevent phantom reads. Isolation levels determine the degree of isolation for a transaction from changes made to resource or data by other transactions. In SQL Server 2008, you can configure the following five types of isolation levels for transactions: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SNAPSHOT, and SERIALIZABLE. The READ UNCOMMITTED isolation level is the least restrictive isolation level. This isolation level allows statements to read rows modified by other transactions but that are not yet committed. The READ COMMITTED isolation level prevents statements from reading data that has been modified by other transactions but not committed. The REPEATABLE READ isolation level prevents statements from reading data that has been modified by other transactions but not yet committed, and it also prevents other transactions from modifying data that has been read by the current transaction until the current transaction completes. The SNAPSHOT isolation level ensures that the data read by any statement in a transaction will remain consistent until the transaction is complete. The SERIALIZABLE isolation is the most restrictive isolation level in which the transactions are completely isolated from one another. Only the SNAPSHOT and SERIALIZABLE isolation levels do not allow any concurrency side effects, such as a dirty reads, phantom reads, or non-repeatable reads. To define an isolation level for transactions, you can use the SET TRANSACTION ISOLATION LEVEL Transact-SQL statement. You cannot configure the READ UNCOMMITTED, READ COMMITTED, and REPEATABLE READ isolation levels to accomplish the desired objective because these isolation levels enable phantom reads.Objective: Monitoring and Troubleshooting SQL Server Sub-Objective: Identify concurrency problems.
References: MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQL Reference > SET (Transact-SQL) > SET TRANSACTION ISOLATION LEVEL (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > Product Documentation > SQL Server 2008 Books Online > Database Engine > Development > Querying and Changing Data (Database Engine) > Accessing and Changing Database Data > Locking and Row Versioning > Managing Concurrent Data Access > Isolation Levels in the Database Engine