Add with (nolock) to stored procedures which grab the oldest job

At sites with huge back logs in tables like rsconnect..tblpendingevent, HEALTHCARE MATERIALS MANAGEMENT SERVICES for example had 14 million rows, the "spTakeOldestOwnedStudyManagementJob" was taking too long to assign ownership and the table kept growing instead of processing due to locking issues.

To get around this we added a (nolock) to the 2 selects of ownership query and since the job (and others like it) grab the oldest job pending there is no reason to lock the entire table.

This is the edited code:

UPDATE tblPendingEvent

SET State = 1 /* STATE_EXECUTING */, OwnershipExpiration = @OwnershipExpiration

FROM (SELECT TOP 1 IDPendingEvent FROM tblPendingEvent t2 (nolock)

WHERE Owner = @Owner AND

TimeExecute <= @Now AND

State <> 3 /* Unrecoverable */ AND

/* Only one event must be pending per target AE Title */

NOT EXISTS (SELECT 1 FROM tblPendingEvent (nolock) t_Pending_For_TargetAETitle

WHERE (t_Pending_For_TargetAETitle.State = 1 /* Executing */ AND t_Pending_For_TargetAETitle.OwnershipExpiration > @now)

AND t2.AETitleHost = t_Pending_For_TargetAETitle.AETitleHost

AND t2.IDPendingEvent <> t_Pending_For_TargetAETitle.IDPendingEvent

)

ORDER BY TimeExecute ASC) t1

WHERE tblPendingEvent.IDPendingEvent = t1.IDPendingEvent;

RETURN @@ROWCOUNT

END

Adding the (nolock) to those 2 selects greatly improved the speed at which these events get handled and despite thousands of new event being inserted daily the back log continues to decrease.