You have a database named Database1. Database1 has two stored procedures named
Proc1 and Proc2 and a table named Table1. Table1 has millions of rows.
Proc1 updates data in Table1. Proc2 reads data from Table1.
You discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is
blocked. The book affects all rows, including those that are not being updated by Proc1.
You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that
Proc1 is not updating.
What should you change Proc1 to do?
More than one answer choice may achieve the goal. Select the BEST answer.
A.
Update less than 4,000 rows simultaneously.
B.
Use the PAGLOCK table hint.
C.
Wait for Proc2 to complete.
D.
Use the ROWLOCK table hint.
LOL. While it is pretty common to batch your updates for some processes it’s not always practical. I’m pretty sure that this question is all about lock escalation and that they’re after: D. Use the ROWLOCK table hint. In practice it might be better to go with PAGLOCK to reduce the number of locks being held.
ROWLOCK is just a hint. It’s not guaranteed to work.
I agree. Answer should be D. A as answer is riduculous
Agree with A. ‘u discover that when Proc1 is executed to update more than 4,000 rows, Proc2 is blocked ‘ implies there is a lock escalation from row to table whenever updating 4000 rows at the same time.
I think I would go with D. Number 5000 is the magic number for lock escalation. Although escalation can happen below 5000 due to memory threshold, without further info we should assume db settings are default.
* “Lock escalation is triggered when lock escalation is not disabled on the table by using the ALTER TABLE SET LOCK_ESCALATION option”…
* “A single Transact-SQL statement acquires at least 5,000 locks on a single nonpartitioned table or index.”
* “The number of locks in an instance of the Database Engine exceeds memory or configuration thresholds.”
https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx#Anchor_0
A. might be right answer. Following support article suggests reducing the batch size is the best approach to prevent lock escalation.
https://support.microsoft.com/en-us/help/323630/how-to-resolve-blocking-problems-that-are-caused-by-lock-escalation-in-sql-server
Great, thanks.
From this article: Using a lock hint such as ROWLOCK only alters the initial lock plan. Lock hints do not prevent lock escalation.
The question specifically says “You need to ensure that when Proc1 is executing, Proc2 can access the data in Table1 that Proc1 is not updating.”
So trying to limit the batch to prevent lock escalation does not “ensure” this. The only answer that meets this specific criteria is D to force row locking.
Correct ANS is A