Administering a SQL Database Infrastructure — Question 168

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?

Answer options

• A. read committed
• B. repeatable read
• C. snapshot
• D. serializable

Correct answer: D

Explanation

The 'serializable' isolation level ensures that no other transactions can access the data being modified until the current transaction is completed, effectively blocking updates. The 'read committed' level allows other transactions to read data that has been committed, while 'repeatable read' allows reading the same data multiple times but does not prevent other transactions from updating it. 'Snapshot' isolation provides a versioned view of the data but does not block other transactions from making changes.