Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Rowlock and primary key

Posted on 2001-07-05
8
Medium Priority
?
540 Views
Last Modified: 2008-03-17
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
Comment
Question by:aderounm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6256050
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
 

Author Comment

by:aderounm
ID: 6256734
The problem is that we call the stored procedure from a multiuser environment and we keep getting "deadlock on lock" errors.
0
 
LVL 2

Expert Comment

by:pkohlmil
ID: 6257263
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.

 

Accepted Solution

by:
aderounm earned 0 total points
ID: 6257385
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6258039
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
 

Expert Comment

by:CleanupPing
ID: 9282085
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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question