Which isolation levels should you use?

DRAG DROP

You administer a Microsoft Azure SQL Database instance.

You are troubleshooting a number of stored procedures that use transactions.

-p_ModifyCustomer modifies customer records in the database. Processes that uses these records must receive a copy of the record as it exists at the beginning of the transaction, and the procedure must not block these processes.

-p_GetOrders is used to retrieve orders for a customer. While the transaction is running, no other process should be able to read the same data, and no other transaction should be able to modify the data until the transaction completes.

-p_ShipOrders is run once per day to batch orders into shipping criteria. While this transaction is running, no other transaction should be allowed to insert data into the range of orders being modified.

You need to choose the appropriate transaction isolation level for each stored procedure. The transaction must meet the need while providing the highest level of concurrency and performance.

Which isolation levels should you use? To answer, drag the appropriate isolation levels to correct stored procedures. Each isolation level 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.

View Answer

Answer:

Explanation:

Box 1: READ_COMMITTED

READ COMMITTED specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option

Box 2: REPEATABLE_READ:

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.

Shared locks are placed on all data read by each statement in the transaction and are held until the transaction completes. This prevents other transactions from modifying any rows that have been read by the current transaction.

Box 3: SERIALIZABLE

SERIALIZABLE Specifies the following:

Statements cannot read data that has been modified but not yet committed by other transactions. No other transactions can modify data that has been read by the current transaction until the current transaction completes. Other transactions cannot insert new rows with key values that would fall in the range of keys read by any statements in the current transaction until the current transaction completes.

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level­transact-sql?view=sql-server-2017