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.
D is the correct answer
Nope! Option A is still the BEST answer. Using ROWLOCK would attempt to grab as many locks, but it won’t resolve the BLOCKING being experienced by Proc2 since ROWLOCKs are exclusive (not, shared) locks.
The issue is only experienced when ROWCOUNT is greater than 4000. Reducing this to less than 4000 rows would resolve the contention,
Skippo
I believe D is correct.
For me “Proc2 can access the data in Table1 that Proc1 is not updating” is crucial, so D is correct in this case.
You mentioned about “but it won’t resolve the BLOCKING being experienced by Proc2” but please be aware that Proc2 wants to access data not updated by Proc1.
You’re quite right, y’know. Proc2 isn’t trying to access the same data for which Proc1 will hold ROWLOCKS.
By specifying the ROWLOCK table hint, Proc1 only locks the Table1 rows it is going to update, and would therefore not ‘block’ Proc2 from accessing (reading) other rows of the table.
Quite right. I believe that this question is about lock escalation. By using rowlock you stop the locks from escalating to table locks. Using paglock might actually be a more practical compromise but for the question I’m going with rowlock.