1

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?

  • 2
    You will probably get better (or more) answers if you tell us which DBMS you are using. – a_horse_with_no_name Apr 19 '12 at 16:53
  • 1
    Sybase or SQL Server (@@spid is a giveaway) – Philᵀᴹ Apr 20 '12 at 8:38
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.