Exam DP-300 topic 4 question 3 discussion

Actual exam question from Microsoft's DP-300

Question #: 3
Topic #: 4

You deploy a database to an Azure SQL Database managed instance.
You need to prevent read queries from blocking queries that are trying to write to the database.
Which database option should set?

A. PARAMETERIZATION to FORCED
B. PARAMETERIZATION to SIMPLE
C. Delayed Durability to Forced
D. READ_COMMITTED_SNAPSHOT to ON

Show Suggested Answer

Suggested Answer: D 🗳️

by Billhardy at Feb. 8, 2021, 10:48 p.m.

Comments

Submit Cancel

Billhardy

Highly Voted 3 years, 3 months ago

Answer looks correct RCSI

upvoted 10 times

...

testdumps2017

Most Recent 10 months ago

I thought RCSI is enabled by default in Azure SQL?

upvoted 1 times

...

D is correct If READ_COMMITTED_SNAPSHOT is set to OFF (the default on SQL Server), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.

upvoted 3 times

Pranava_GCP

1 year, 1 month ago

If READ_COMMITTED_SNAPSHOT is set to ON (the default on Azure SQL Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions. https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16#arguments

upvoted 1 times

...

alexatl

1 year, 4 months ago

D looks right

upvoted 1 times

...

klever

3 years, 3 months ago

I will go for D since A and B are more about query performance and C I simply rule out because have not heard about it. Regarding D: If READ_COMMITTED_SNAPSHOT is set to ON (the default on Azure SQL Database), the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

upvoted 3 times

...