70-463 · Question #118
You are running a training exercise for Microsoft SQL Server 2012 junior administrators. You are discussing the effects of setting IsolationLevel transaction property of a Data Flow task to Chaos…
The correct answer is A. It prevents pending changes from more highly isolated transactions from being overwritten. The Chaos isolation level in SQL Server prevents pending changes from higher-isolation transactions from being overwritten, but allows dirty reads and does not issue shared locks.
Question
You are running a training exercise for Microsoft SQL Server 2012 junior administrators. You are discussing the effects of setting IsolationLevel transaction property of a Data Flow task to Chaos. Which of the following suitably describes the effect?
Options
- AIt prevents pending changes from more highly isolated transactions from being overwritten.
- BIt prevents shared locks from being issued and exclusive locks from being honored.
- CIt prevents other users from updating the data.
- DIt reduces blocking by storing a version of data that one application can read while another is
How the community answered
(55 responses)- A93% (51)
- B4% (2)
- C2% (1)
- D2% (1)
Why each option
The Chaos isolation level in SQL Server prevents pending changes from higher-isolation transactions from being overwritten, but allows dirty reads and does not issue shared locks.
The Chaos isolation level specifically prevents overwrites of pending changes from more highly isolated transactions, meaning a Chaos-level transaction cannot overwrite uncommitted changes made by transactions at higher isolation levels such as Serializable or Repeatable Read. It is the lowest practical isolation level available in this context.
Preventing shared locks from being issued and exclusive locks from being honored describes behavior closer to a no-lock or ReadUncommitted hint scenario, not the Chaos isolation level definition.
Preventing other users from updating data describes Repeatable Read or higher isolation levels, which place shared locks on read data.
Storing a version of data so one application can read while another modifies describes Read Committed Snapshot Isolation (RCSI) or Snapshot isolation, not Chaos.
Concept tested: SQL Server Chaos transaction isolation level behavior
Source: https://learn.microsoft.com/en-us/dotnet/api/system.data.isolationlevel
Topics
Community Discussion
No community discussion yet for this question.