You work as a Database Administrator (DBA) for a company named ABC.com. The
company uses a Microsoft SQL Server 2012 infrastructure.
You have a database named CorpDB. You plan to create a stored procedure to access data
in CorpDB.
You need to ensure that the no other transactions can modify data that has been read by
the stored procedure until the current transaction completes.
What should you do?
A.
You should configure the stored procedure to use the READ UNCOMMITTED isolation level.
B.
You should configure the stored procedure to use the READ COMMITTED isolation level.
C.
You should configure the stored procedure to use the SERIALIZABLE isolation level.
D.
You should configure the stored procedure to use the REPEATABLE READ isolation level.
E.
You should configure the stored procedure to use the SNAPSHOT isolation level.
Explanation:
REPEATABLE READ isolation, while ensuring you don’t get DIRTY READS, can still result in PHANTOM READS.
The best option (if you’re not trying to reduce lock impact/cost), is SERIALIZABLE isolation level.
agreed Serializable
D
The requirement is “You need to ensure that the no other transactions can modify data that has been read by the stored procedure”. which is exactly what repeatable read does.
E – Serializable.
Repetable Read allows modifinig the data but will still read the same data.
Serializable blocks the modify options
•under READ COMITTED, the second SELECT may return any data. A concurrent transaction may update the record, delete it, insert new records. The second select will always see the new data.
•under REPEATABLE READ the second SELECT is guaranteed to see the rows that has seen at first select unchanged. New rows may be added by a concurrent transaction in that one minute, but the existing rows cannot be deleted nor changed.
•under SERIALIZABLE reads the second select is guaranteed to see exactly the same rows as the first. No row can change, nor deleted, nor new rows could be inserted by a concurrent transaction