Exam 70-762 topic 1 question 128 discussion

I think C is correct: statements within thestored procedure should only show changes made by the stored procedure - that IMHO means no repeatable reads and no phantom reads.

The question in fact sounds like this: The following requirements must be met during the entire execution of the stored procedure: - The stored procedure must only read changes that are persisted to the database. - SELECT statements within the stored procedure should only show changes to the data that are made by the stored procedure. Obviously, READ UNCOMMITED cannot be used here because of the first requirement. So forget about options A and D. READ COMMITTED cannot be used because of the second reqirement. As you know, when READ COMMITTED is used, data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. Only SERIALIZABLE can guarantee that both conditions are met. So C is correct.

The answer is clearly C.

Answer is C. "Statements within the stored procedure should only show changes to the data that are made by the stored procedure." If it was Read Committed then other transactions can still update the data. With Serializable the other transactions are locked out, hence why Serializable is very consistent but not concurrent.

I think A is correct. We are setting the transaction isolation level to read uncommitted but after that, the database isolation level is changed to Read Committed Snapshot. transaction isolation level cannot override Read Committed Snapshot (https://dba.stackexchange.com/questions/229582/is-it-possible-to-override-a-readcommittedsnapshot-isolation-level-with-readcomm) and because of that, transaction will use RCS Isolation level.

What C does is that it ensures that query returns the same results each time that it is run during the stored procedure.

The answer is A: SET READ_COMMITED_SNAPSHOT ON because the stored procedure has to read changes it self commits.

SET READ_COMMITTED_SNAPSHOT ON; With this setting enabled, all queries that normally execute using the READ COMMITTED isolation level switch to using the READ_COMMITTED_SNAPSHOT isolation level without requiring you to change the query code. SQL Server creates a snapshot of committed data when each statement starts. Consequently, read operations at different points in a transaction might return different results. During the transaction, SQL Server copies rows modified by other transactions into a collection of pages in tempdb known as the version store. When a row is updated multiple times, a copy of each change is in the version store. This set of row versions is called a version chain.