Should I use table hints with a process-keyed table?

Question

I read on stackoverflow that locking can be a problem when using process-keyed tables, and I was wondering if you had any suggestions on how to minimize or avoid unnecessary contention (e.g. table locks) when using these types of tables. Here's some background:

I'm developing a batch process that inserts the rows resulting from a stored procedure call into a table. I have no control over the stored procedure. The table is keyed by process id (@@spid) because it needs to be read by several stored procedures that follow and should not conflict with other batches running at the same time. This shouldn't be too big a problem because it is a low volume server, e.g. not too many of these batches will be running simultaneously. Still, I wouldn't want to create table locks if it can be avoided as I imagine blocking may cause unexpected failures or delays.

Any advice?

Answer 1

You said

.. no control over the stored procedures

.. low volume server, e.g. not too many of these batches will be running simultaneously.

Therefore, I'd do nothing. Hints should only be added very judiciously when required. As it stands, you have no known issue to solve.

If you still want to add a hint, then simply use ROWLOCK to disallow table locks.