Rowlock and primary key

Is it necessary for a table to have a primary key in order to use Rowlock in a stored procedure that makes changes to one row in the table? If the answer is yes, is it possible for the primary key to be composed.
aderounmAsked:
Who is Participating?
 
aderounmConnect With a Mentor Author Commented:
It looks like we have solved the problem. We added an index to the table by creating a primary key and everything ran perfectly. No more deadlocks. It seems that you can have a ROWLOCK on any table but it prevents multiuser deadlocks only if used on a table with an index.
0
 
nigelrivettCommented:
You don't need a primary key for rowlock - all it does is just locks rows instead of pages.

You could use an identity for a primary key - although you should already have fields that could compose it.
0
 
aderounmAuthor Commented:
The problem is that we call the stored procedure from a multiuser environment and we keep getting "deadlock on lock" errors.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
pkohlmilCommented:
Are you setting LOCK_TIMEOUT to zero? I'm thinking that using a different value on SET LOCK_TIMEOUT might help.
0
 
nigelrivettCommented:
rowlock takes locks on rows only but that doesn't help if you are table scanning as you have to access all pages anyway. Design is the way to avoid deadlocks - i.e. design the processes so that they don't conflict.

pkohlmil
deadlocks are nothing to do with timeouts - quite the reverse.
0
 
CleanupPingCommented:
aderounm:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.