Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 14 discussion

Actual exam question from Microsoft's 70-762

Question #: 14
Topic #: 1

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.
Select and Place:

Show Suggested Answer

Suggested Answer:

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/

by ClydeZ at Dec. 17, 2019, 11:28 p.m.

Comments

Submit Cancel

gtc108

Highly Voted 5 years, 3 months ago

1.Serializable 2.Read Uncommitted 3.Repeatable Read

upvoted 29 times

Nickname17

5 years, 2 months ago

You must avoid unnecessary locks

upvoted 1 times

...

Nickname17

5 years, 2 months ago

ensure that lost updates do not occur

upvoted 1 times

...

delgadillo

4 years, 11 months ago

Can be 1.Committed 2.Read Uncommitted 3.Repeatable Read

upvoted 16 times

...

moehijawe

Highly Voted 5 years, 4 months ago

third box must be repeatable read since phantom read is allowed (insert new records is allowed)

upvoted 22 times

JohnFan

5 years, 3 months ago

the question states "interested" instead of "inserted"

upvoted 2 times

...

JohnFan

5 years, 3 months ago

When you set the REPEATABLE READ isolation level, you ensure that any data read by one transaction is not changed by another transaction. That way, the transaction can repeat a query and get identical results each time.

upvoted 1 times

...

BabyBee

4 years, 5 months ago

Repeatable Read The behavior of the REPEATABLE READ isolation level is much like that of READ COMMITTED, except that it ensures that multiple reads of the same data within a transaction is consistent. Dirty reads and non-repeatable reads are prevented, although phantom reads are a possible side effect because range locks are not used. The SERIALIZABLE isolation level behaves like REPEATABLE READ, but goes one step further by ensuring new rows added after the start of the transaction are not visible to the transaction’s statement. Therefore, dirty reads, non-repeatable reads, and phantom reads are prevented.

upvoted 1 times

...

TheDUdeu

Most Recent 4 years, 6 months ago

Last one is repeatable everything else is correct.

upvoted 2 times

...

Tinashe16

4 years, 6 months ago

The provided answer is correct. We can’t use Read committed on last option because it doesn’t prevent phantom records. https://www.mssqltips.com/sqlservertip/4438/compare-repeatable-read-and-serializable-sql-server-transaction-isolation-levels/

upvoted 1 times

Tinashe16

4 years, 6 months ago

***I mean we can’t use Repeatable read

upvoted 1 times

...

Andy7622

4 years, 9 months ago

The third box is definitely "Repeatable_read" Second is definitely "Read Uncommited", But in first the expression "Accurate data is top priority " is a bit confusing .

upvoted 1 times

...

smartrammy

4 years, 9 months ago

Box 3 says Identical results are returned even when new rows are inserted. So Serializable Isolation Level avoids Phantom read . So third box has to be Serializable Box 2 can be Read Uncommitted Box 1 can Read Committed

upvoted 1 times

NickMane

4 years, 9 months ago

serializable isolation prevents new rows being inserted, repeatable reads allows it. correct answer is: Read Committed, Read Uncommitted, Repeatable Read

upvoted 4 times

...

julie2020

4 years, 10 months ago

1.Committed 2.Read Uncommitted 3.Repeatable Read

upvoted 6 times

...

stm22

4 years, 11 months ago

process of elimination: Box 2 is certainly read uncommitted Box 3 is Repeatable because it mentions "multiple" and "new records are allowed". Box 1 is Read Committed. It can not be Serializable because it mentions "select statement will wait". Read Committed Because this isolation level only reads committed data, dirty reads are prevented. However, if query reads the same data multiple times, non-repeatable reads or phantom reads are possible. Repeatable Read The behavior of the REPEATABLE READ isolation level is much like that of READ COMMITTED, except that it ensures that multiple reads of the same data within a transaction is consistent. Dirty reads and non-repeatable reads are prevented, although phantom reads are a possible side effect because range locks are not used. Serializable The SERIALIZABLE isolation level behaves like REPEATABLE READ, but goes one step further by ensuring new rows added after the start of the transaction are not visible to the transaction’s statement. Therefore, dirty reads, non-repeatable reads, and phantom reads are prevented.

upvoted 11 times

LeonLeon

4 years, 11 months ago

Correct and the same as the given answer! https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/transaction-isolation-levels?view=sql-server-ver15

upvoted 2 times

...

melvin9900

4 years, 10 months ago

Agreed

upvoted 2 times

...

eggzamtaker

4 years, 5 months ago

THIS **IS** THE CORRECT ANSWER. None of the other 25+ posts have any sense in them. People are all over the place. Wth is going on?

upvoted 1 times

...

strikersree

4 years, 12 months ago

Read Committed, Read Uncommitted, Repeatable Read

upvoted 14 times

...

Twigit

5 years, 4 months ago

Box 1 is Serializable because you cannot read data until the data is committed or rolled back Box 3 is Read Committed because while the transaction is occurring you can read from the data and the same results are returned

upvoted 2 times

Twigit

5 years, 4 months ago

Nevermind, i belive moehijawe is correct. Box 3 should be Repeatable Read

upvoted 2 times

...

ClydeZ

5 years, 6 months ago

The question said accuracy is the top priority, why not using the Serializable isolation level?

upvoted 4 times

Bartek

5 years, 5 months ago

Should be Reeatable Read despite Serializable. Becouse Serializable is blocking all of phenomens. In exercise we can read that to no block 'Phenomen-reads' ("New records are ALLOWED to be inserted into the table referenced by select statement")

upvoted 7 times

JohnFan

5 years, 3 months ago

ClydeZ talks about Box1, Bartek answers Box3

upvoted 6 times

...

JohnFan

5 years, 3 months ago

Because it also says, "Select statements will wait untill any transaction that currently owns the data has been committed or rolled back before returnning the value". READ COMMITTED is the default isolation level for SQL Server. It uses pessimistic locking to protect data. With this isolation level set, a transaction cannot read uncommitted data that is being added or changed by another transaction. A transaction attempting to read data that is currently being changed is blocked until the transaction changing the data releases the lock. Whereas under serializable, new records are not allowed! (Not just wait!) to be interested into the tabe referenced by the Select statement. The most pessimistic isolation level is SERIALIZABLE, which uses range locks on the data to not only prevent changes but also insertions.

upvoted 4 times

JohnFan

5 years, 3 months ago

In contrast, Serializable prevents any other transactions from inserting or modifying the data for the duration of a transaction. Each transaction is completely isolated from one another even when they execute in parallel or overlap.

upvoted 1 times

...