You are the database administrator of your company.
You are in the process of configuring transaction isolationlevel to define the locking and row versioning behavior of Transact-SQL statements.
You want to ensure thatphantom 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 answerrepresents 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 preventphantom reads. Isolation levels determine the degree of isolation for a transaction from changes made toresource or data by other transactions. In SQL Server 2008, you can configure the following five types of isolationlevels for transactions:
READ UNCOMMITTED , READ COMMITTED , REPEATABLE READ , SNAPSHOT , and SERIALIZABLE . The READ UNCOMMITTED isolation level is the least restrictive isolation level. This isolationlevel allows statements to read rows modified by other transactions but that are not yet committed. The READCOMMITTED isolation level prevents statements from reading data that has been modified by other transactionsbut not committed. The REPEATABLE READ isolation level prevents statements from reading data that hasbeen modified by other transactions but not yet committed, and it also prevents other transactions from modifyingdata 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 thetransaction is complete. The SERIALIZABLE isolation is the most restrictive isolation level in which thetransactions are completely isolated from one another. Only the SNAPSHOT and SERIALIZABLE isolation levelsdo not allow any concurrency side effects, such as a dirty reads, phantom reads, or non-repeatable reads. Todefine 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 REA D isolationlevels to accomplish the desired objective because these isolation levels enable phantom reads.Objective:
Monitoring and Troubleshooting SQL ServerSub-Objective:
Identify concurrency problems.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > SET (Transact-SQL) > SET TRANSACTION ISOLATION LEVEL (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Querying and ChangingData (Database Engine) > Accessing and Changing Database Data > Locking and Row Versioning > ManagingConcurrent Data Access > Isolation Levels in the Database Engine