• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 545
  • Last Modified:

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.
0
aderounm
Asked:
aderounm
1 Solution
 
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
 
pkohlmilCommented:
Are you setting LOCK_TIMEOUT to zero? I'm thinking that using a different value on SET LOCK_TIMEOUT might help.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
aderounmAuthor 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:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now