To answer this question, we need to understand the concept of isolation levels and how they affect locking behavior in a database system.
Isolation levels determine the degree to which one transaction is isolated from the effects of other concurrent transactions. They control how locks are acquired and released on data during a transaction.
Let's go through each option to understand why it is correct or incorrect:
Option A) Uncommitted Read - This option is incorrect because the Uncommitted Read isolation level allows a transaction to read uncommitted changes made by other transactions. However, it does not lock the rows scanned to build a result data set.
Option B) Cursor Stability - This option is incorrect because the Cursor Stability isolation level allows a transaction to hold locks on rows that are being accessed via a cursor. However, it does not lock all rows scanned to build a result data set. It only locks the rows being accessed through the cursor.
Option C) Read Stability - This option is incorrect because the Read Stability isolation level allows a transaction to acquire read locks on the data it accesses. These read locks prevent other transactions from modifying the data. However, it does not lock all rows scanned to build a result data set. It only locks the rows accessed by the transaction.
Option D) Repeatable Read - This option is correct because the Repeatable Read isolation level locks all rows scanned to build a result data set. It ensures that other transactions cannot modify or delete the rows that are being scanned by the transaction. This level of isolation provides a higher degree of data integrity but can result in increased locking and potential deadlock situations.
The correct answer is D) Repeatable Read. This option is correct because it locks all rows scanned to build a result data set, ensuring data integrity and preventing other transactions from modifying the rows during the transaction.