Exam 70-762 All Questions

View all questions & answers for the 70-762 exam

Exam 70-762 topic 1 question 95 discussion

Actual exam question from Microsoft's 70-762

Question #: 95
Topic #: 1

You have a reporting application that uses a table named Table1. You deploy a new batch update process to perform updates to Table1.
The environment is configured with the following properties:
✑ The database is configured with the default isolation setting.
✑ The application and process use the default transaction handling.
You observe the application cannot access any rows that are in use by the process.
You have the following requirements:
✑ Ensure the application is not blocked by the process.
✑ Ensure the application has a consistent view of the data
✑ Ensure the application does not read dirty data.
You need to resolve the issue and meet the requirements with the least amount of administrative effort.
What should you do?

A. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application for the SERIALIZABLE isolation level.
B. Enable the database for the READ_COMITTED_SNAPSHOT isolation level.
C. Enable the application for the WITH (NOLOCK) hint.
D. Enable the database for the ALLOW_SNAPSHOT_ISOLATION isolation level. Modify the application and the update process for the SNAPSHOT isolation level.

Show Suggested Answer

Suggested Answer: B 🗳️
Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. This activates the mechanism for storing row versions in the temporary database (tempdb).
READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions. Other transactions can still modify, insert, or delete data between executions of individual statements within the current transaction, resulting in non-repeatable reads, or "phantom" data.
Incorrect Answers:
A: SERIALIZABLE is the most restrictive isolation level, because it locks entire ranges of keys and holds the locks until the transaction is complete. It encompasses REPEATABLE READ and adds the restriction that other transactions cannot insert new rows into ranges that have been read by the transaction until the transaction is complete.
References:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

by EdwardWang at Dec. 30, 2019, 7:54 p.m.

Comments

Submit Cancel

EdwardWang

Highly Voted 5 years, 5 months ago

D is right

upvoted 7 times

New_user

5 years, 5 months ago

Do you consider dirty data problem?

upvoted 5 times

gripasha

5 years, 4 months ago

so what is the conclusion of your useless talk? is D right or not?

upvoted 5 times

Bartek

5 years, 4 months ago

B is right

upvoted 8 times

rya

5 years, 4 months ago

B is right

upvoted 8 times

...

Cococo

Highly Voted 4 years, 9 months ago

Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level. (that's what we need) If the READ_COMMITTED_SNAPSHOT option is set to OFF, you must explicitly set the Snapshot isolation level for each session in order to access versioned rows. (we don't want to configure every session) https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

upvoted 7 times

...

TheDUdeu

Most Recent 4 years, 5 months ago

The answer is B.

upvoted 3 times

...

Alex5x

4 years, 7 months ago

The answer is B: ALTER DATABASE MyDB SET READ_COMMITTED_SNAPSHOT ON; As you can see, this requires only one action, so it's very simple. If you want to use SNAPSHOT isolation level (Option D), then you should make more changes: ALTER DATABASE MyDB SET ALLOW_SNAPSHOT_ISOLATION ON; Change all the SPs involved by adding: SET TRANSACTION ISOLATION LEVEL SNAPSHOT; Since the question says: "meet the requirements with the least amount of administrative effort." we can cross out the option D.

upvoted 7 times

...

cornford

4 years, 11 months ago

Both B and D would work. It does state "with the least amount of administrative effort" which would be B. Marginal in this case as you only additionally edit one procedure, but a consideration if you have 100's of procedures and you are happy with all the procedures operating with optimistic locking.

upvoted 5 times

databasejamdown

4 years, 8 months ago

What is tricky though is that the application must have a consistent view of the data. This means no non-repeatable reads. Read-Committed allows repeatable reads. So I think the answer is D

upvoted 1 times

kiri2020

4 years, 7 months ago

no, it will not use repeatable reads transaction isolation level

upvoted 1 times

...

MML

4 years, 11 months ago

Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions. So D is the right answer

upvoted 2 times

...

amar111

5 years, 1 month ago

There is no transaction Isolation like - READ COMMITTED SNAPSHOT

upvoted 1 times

kiri2020

4 years, 7 months ago

it is on database level https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

upvoted 1 times

...