Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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