What are the consistency and concurrency implications o…

DRAG DROP
You are analyzing the performance of a database environment.
Applications that access the database are experiencing locks that are held for a large amount of time. You are
experiencing isolation phenomena such as dirty, nonrepeatable and phantom reads.
You need to identify the impact of specific transaction isolation levels on the concurrency and consistency of
data.
What are the consistency and concurrency implications of each transaction isolation level? To answer, drag the
appropriate isolation levels to the correct locations. Each isolation level 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.
Select and Place:

Answer:

Explanation:
Read Uncommitted (aka dirty read): A transaction T1executing under this isolation level can access data
changed by concurrent transaction(s).
Pros: No read locks needed to read data (i.e. no reader/writer blocking). Note, T1 still takes transaction duration
locks for any data modified.
Cons: Data is notguaranteed to be transactionally consistent.
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 when accessed multiple times within the same transaction.
Repeatable Read: A transaction T1 executing under this isolation level can only access committed data with an
additional guarantee that any data read cannot change (i.e. it is repeatable) for the duration of the transaction.
Pros: Higher data consistency.
Cons: Locking and blocking. The S locks are held for the duration of the transaction that can lower the
concurrency. It does not protect against phantom rows.
Serializable: A transaction T1 executing under this isolation 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 predicate column) 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.
https://blogs.msdn.microsoft.com/sqlcat/2011/02/20/concurrency-series-basics-of-transactionisolation-levels/