DRAG DROP
You plan to deploy two stored procedures named SP1 and SP2 that read data from the
database.
Your company identifies the following requirements for each stored procedure:
SP1 must allow dirty reads.
SP2 must place range locks on the data to ensure read consistency.
You need to identify which isolation level you must set for each stored procedure. The
solution must minimize the number of locks.
Which isolation level should you identify?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer
area. (Answer choices may be used once, more than once, or not at all.)
Answer: 
Explanation:
Note:
* READ UNCOMMITTED
Specifies that statements can read rows that have been modified by other transactions but
not yet committed.
Transactions running at the READ UNCOMMITTED level do not issue shared locks to
prevent other transactions from modifying data read by the current transaction. READ
UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the
current transaction from reading rows that have been modified but not committed by other
transactions. When this option is set, it is possible to read uncommitted modifications, which
are called dirty reads. Values in the data can be changed and rows can appear or disappear
in the data set before the end of the transaction. This option has the same effect as settingNOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive
of the isolation levels.
* 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.
Range locks are placed in the range of key values that match the search conditions of each
statement executed in a transaction. This blocks other transactions from updating or
inserting any rows that would qualify for any of the statements executed by the current
transaction. This means that if any of the statements in a transaction are executed a second
time, they will read the same set of rows. The range locks are held until the transaction
completes. This is the most restrictive of the isolation levels because it locks entire ranges of
keys and holds the locks until the transaction completes. Because concurrency is lower, use
this option only when necessary.
Reference: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
What about the “The solution must minimize the number of locks” requirement?
Why not to use “snapshot” for SP2? It provides read consistency and doesnt
locks the data.
0
0
Hello,
“The solution must minimize the number of locks” requirement is to be combined with other requirement “SP2 must place range locks on the data to ensure read consistency.” which force to use of:
SP2 : SERIALIZABLE
as said in Explanation quoting https://msdn.microsoft.com/en-us/library/ms173763.aspx (SET TRANSACTION ISOLATION LEVEL (Transact-SQL)), only SERIALIZABLE can handle range locks.
a+,=)
-=Clement=-
0
0
1. READ UNCOMMITTED
2. SNAPSHOT
0
0
snapshot the question says solution must minimize the number of locks so what we want is optimistic locking and that’s why . Serializable does pessimistic locking
0
0
In serializable isolation level, SQL Server acquires key range locks and holds them until the end of the transaction. A key range lock ensures that, once a transaction reads data, no other transaction can alter that data – not even to insert phantom rows – until the transaction holding the lock completes. In snapshot isolation level, SQL Server does not acquire any locks.
so Serializable in sp2
0
0
Snapshot can be used as an alternative to repeatable read and serializable. So snapshot shoul be the answer
0
0
SP2 must place range locks on the data to ensure read consistency.
Range Locks are only applied for isolation level serializable
0
0
But then, the Range Locks are only needed on SP2 for Read Consistency. If there won’t be updates (need for UPDLOCK), then Snapshot isolation would ensure this other requirement “The solution must minimize the number of locks.”
0
0