You use SQL Azure to store data used by an e-commerce application.
You develop a stored procedure named sp1. Sp1 is used to read and change the price of all
the products sold on the e-commerce site.
You need to ensure that other transactions are blocked from updating product data while sp1
is executing.
Which transaction isolation level should you use in sp1?
A.
Repeatable read
B.
Read committed
C.
Serializable
D.
Snapshot
Why not Repeatable Read because question is saying You need to ensure that other transactions are blocked from updating(not inserting) product data while sp1 is executing.
If there is no insert there is no chance of Phantom Read. So i think A is better option
Repeatable Read, while ensuring concurrency, can still lead to Phantom Read. A transaction that needs to prevent UPDATES (Delete + Insert), will be less concurrent and more consistent… which RR doesn’t guarantee.
The best option is Serializable. If the question didn’t specify to BLOCK TRANSACTIONS FROM PERFORMING UPDATES, then Snapshot Isolation would have been best.
Agree: A
https://msdn.microsoft.com/en-us/library/ms173763.aspx
REPEATABLE READ
Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
The difference between Repeatable Read and Serializable is that Serializable also takes out range locks based on the predicate of the query so that additional rows which would satisfy the query’s predicate cannot be added. This does not seem to be required by the terms of this question so… I think a) Repeatable Read.
I believe Serializable is correct as it will block other transactions and will also not allow phantom rows.
Read Committed will allow other transactions to update and insert new rows that matches the query data-set.
Like Skippo mentioned updating the product data means issuing DML commands such as UPDATE, DELETE against this table. Repeatable Read allows phantom read.
Blocking other transactions rules out Snapshot.