Which isolation level should you use for each scenario?

DRAG DROP

You are evaluating the performance of a database environment. You must avoid unnecessary locks and ensure that lost updates do not occur. You need to choose the transaction isolation level for each data scenario.

Which isolation level should you use for each scenario? To answer, drag the appropriate isolation levels to the correct scenarios. Each isolation may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

View Answer

Answer:

Explanation:

Box 1: Read committed Read Committed: A transaction T1 executing under this isolation level can only access committed data. Pros: Good compromise between concurrency and consistency. Cons: Locking and blocking. The data can change whenaccessed multiple times within the same transaction.

Box 2: Read Uncommitted Read Uncommitted (aka dirty read): A transaction T1 executing under this isolation level can access data changed by concurrent transaction(s). Pros: No read locks needed to readdata (i.e. no reader/writer blocking). Note, T1 still takes transaction duration locks for any data modified. Cons: Data is not guaranteed to be transactionally consistent.

Box 3: Serializable Serializable: A transaction T1 executing under thisisolation level provides the highest data consistency including elimination of phantoms but at the cost of reduced concurrency. It prevents phantoms by taking a range lock or table level lock if range lock can’t be acquired (i.e. no index on the predicatecolumn) for the

duration of the transaction. Pros: Full data consistency including phantom protection. Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the concurrency.

References: https://blogs.msdn.microsoft.com/sqlcat/2011/02/20/concurrency-series-basics-of-transaction­isolation-levels/